Dodatek Power Query jest wbudowany w wersje Office 365, Excel 2016, Excel 2019 i jest dostępny do bezpłatnego pobrania w wersjach programu Excel 2010 i Excel 2013 dla systemu Windows. Narzędzie jest przeznaczone do wyodrębniania, przekształcania i ładowania danych do programu Excel z różnorodność źródeł. Najlepsza część: dodatek Power Query zapamiętuje Twoje kroki i odtworzy je, gdy zechcesz odświeżyć dane. Oznacza to, że możesz wyczyścić dane pierwszego dnia w 80% normalnego czasu, a możesz wyczyścić dane w dniach od 2 do 400, klikając po prostu Odśwież.
Mówię to o wielu nowych funkcjach programu Excel, ale to naprawdę najlepsza funkcja, jaka pojawiła się w programie Excel od 20 lat.
Podczas moich seminariów na żywo opowiadam historię o tym, jak wymyślono dodatek Power Query jako podpórka dla klientów usług SQL Server Analysis Services, którzy zostali zmuszeni do korzystania z programu Excel w celu uzyskania dostępu do dodatku Power Pivot. Jednak dodatek Power Query był coraz lepszy, a każda osoba korzystająca z programu Excel powinna poświęcić czas na naukę dodatku Power Query.
Pobierz dodatek Power Query
Być może masz już dodatek Power Query. Znajduje się w grupie Pobierz & przekształcanie na karcie Dane.
Ale jeśli korzystasz z programu Excel 2010 lub Excel 2013, przejdź do Internetu i wyszukaj pozycję Pobierz dodatek Power Query. Twoje polecenia dodatku Power Query pojawią się na dedykowanej karcie dodatku Power Query na Wstążce.
Wyczyść dane po raz pierwszy w dodatku Power Query
Aby podać przykład niektórych niesamowitych zalet dodatku Power Query, załóżmy, że codziennie otrzymujesz plik pokazany poniżej. Kolumna A nie jest wypełniona. Ćwiartki przechodzą w poprzek zamiast w dół strony.
Aby rozpocząć, zapisz ten skoroszyt na dysku twardym. Umieść go w przewidywalnym miejscu z nazwą, której będziesz używać dla tego pliku każdego dnia.
W programie Excel wybierz opcję Pobierz dane, z pliku, ze skoroszytu.
Przejdź do skoroszytu. W okienku podglądu kliknij Arkusz1. Zamiast klikać Wczytaj, kliknij Edytuj. Teraz skoroszyt jest wyświetlany w nieco innej siatce - w siatce dodatku Power Query.
Teraz musisz naprawić wszystkie puste komórki w kolumnie A.Jeśli miałbyś to zrobić w interfejsie użytkownika programu Excel, nieporęczna sekwencja poleceń to Strona główna, Znajdź i wybierz, Przejdź do specjalnego, Puste, Równe, Strzałka w górę, Ctrl + Enter .
W dodatku Power Query wybierz opcję Przekształć, wypełnij, w dół.
Wszystkie wartości null są zastępowane wartościami z góry. W przypadku dodatku Power Query potrzeba trzech kliknięć zamiast siedmiu.
Następny problem: kwartały przechodzą w poprzek zamiast w dół. W programie Excel można to naprawić za pomocą tabeli przestawnej z wieloma zakresami konsolidacji. Wymaga to 12 kroków i ponad 23 kliknięć.
W dodatku Power Query wybierz dwie kolumny, które nie są kwartałami. Otwórz menu rozwijane Unpivot Columns na karcie Transform i wybierz opcję Unpivot Other Columns, jak pokazano poniżej.
Kliknij prawym przyciskiem myszy nowo utworzoną kolumnę Atrybut i zmień jej nazwę Quarter zamiast Attribute. Ponad dwadzieścia kliknięć w programie Excel staje się pięcioma kliknięciami w dodatku Power Query.
Aby być uczciwym, nie każdy krok czyszczenia jest krótszy w dodatku Power Query niż w programie Excel. Usunięcie kolumny nadal oznacza kliknięcie kolumny prawym przyciskiem myszy i wybranie opcji Usuń kolumnę. Ale szczerze mówiąc, historia tutaj nie dotyczy oszczędności czasu w pierwszym dniu.
Ale poczekaj: dodatek Power Query zapamiętuje wszystkie Twoje kroki
Spójrz na prawą stronę okna Power Query. Istnieje lista o nazwie Zastosowane kroki. Jest to natychmiastowa ścieżka audytu wszystkich Twoich kroków. Kliknij ikonę koła zębatego, aby zmienić swoje wybory w tym kroku i wprowadzić zmiany do kolejnych kroków. Kliknij dowolny krok, aby zobaczyć, jak wyglądały dane przed tym krokiem.
Po zakończeniu czyszczenia danych kliknij Zamknij i załaduj, jak pokazano poniżej.
Wskazówka
Jeśli dane mają więcej niż 1048576 wierszy, możesz użyć menu rozwijanego Zamknij i załaduj, aby załadować dane bezpośrednio do modelu danych dodatku Power Pivot, który może pomieścić 995 milionów wierszy, jeśli masz wystarczającą ilość pamięci zainstalowanej na komputerze.
W ciągu kilku sekund przekształcone dane pojawią się w programie Excel. Niesamowite.
Efekt: czyste dane jutro jednym kliknięciem
Ale znowu, historia dodatku Power Query nie dotyczy oszczędności czasu w dniu 1. Po wybraniu danych zwróconych przez dodatek Power Query po prawej stronie programu Excel pojawia się panel Zapytania i połączenia, a na nim przycisk Odśwież. (Potrzebujemy tutaj przycisku Edytuj, ale ponieważ go nie ma, musisz kliknąć prawym przyciskiem myszy oryginalne zapytanie, aby wyświetlić lub wprowadzić zmiany w pierwotnym zapytaniu).
Fajnie jest czyścić dane pierwszego dnia. Uwielbiam robić coś nowego. Ale kiedy mój menedżer widzi wynikowy raport i mówi „Pięknie. Czy możesz to robić codziennie? ” Szybko nienawidzę nudy codziennego czyszczenia tego samego zestawu danych.
Tak więc, aby zademonstrować 400 dzień czyszczenia danych, całkowicie zmieniłem oryginalny plik. Nowe produkty, nowi klienci, mniejsze liczby, więcej wierszy, jak pokazano poniżej. Zapisuję tę nową wersję pliku w tej samej ścieżce i pod tą samą nazwą co oryginalny plik.
Jeśli otworzę skoroszyt kwerendy i kliknę przycisk Odśwież, w ciągu kilku sekund dodatek Power Query zgłosi 92 wiersze zamiast 68 wierszy.
Czyszczenie danych w dniu 2, dniu 3, dniu, 4,… dniu 400,… dniu Infinity wymaga teraz dwóch kliknięć.
Ten jeden przykład zarysowuje tylko powierzchnię dodatku Power Query. Jeśli spędzisz dwie godziny z książką, M jak (Data) Monkey autorstwa Kena Pulsa i Miguela Escobara, dowiesz się o innych funkcjach, takich jak te:
- Łączenie wszystkich plików Excel lub CSV z folderu w jedną siatkę Excela
- Konwersja komórki z jabłkiem; bananem; wiśnią; koprem; bakłażanem na pięć wierszy w programie Excel
- Wykonywanie WYSZUKAJ.PIONOWO w skoroszycie wyszukiwania podczas wprowadzania danych do dodatku Power Query
- Przekształcanie pojedynczego zapytania w funkcję, którą można zastosować do każdego wiersza w programie Excel
Aby uzyskać pełny opis Power Query, zobacz M Is for (Data) Monkey autorstwa Kena Pulsa i Miguela Escobara. Pod koniec 2019 r. Dostępna będzie druga edycja pod nazwą Master Your Data.
Podziękowania dla Miguela Escobara, Roba Garcii, Mike'a Girvina, Raya Hausera i Colina Michaela za nominację Power Query.
