Power Query i Power Pivot to dwa osobne narzędzia w Excelu. Power Query importuje dane i je czyści. Power Pivot łączy kilka tabel, abyś mógł podsumować je jako jedną. Wiedza, po które narzędzie sięgnąć, oszczędza godziny powtarzalnej, ręcznej pracy.
Power Query: wyczyść raz, odświeżaj zawsze
Zaczynasz na karcie Dane, od przycisku Pobierz dane. Power Query potrafi wczytać plik CSV lub Excela, cały folder plików, bazę danych albo stronę internetową. Dane otwierają się w Edytorze Power Query, gdzie usuwasz puste wiersze, dzielisz kolumny, zmieniasz typy, filtrujesz i nie tylko.
Każde działanie zapisuje się jako zastosowany krok na liście po prawej stronie. Kliknij krok, aby zobaczyć dane w tym momencie, albo usuń krok, którego już nie chcesz. Gdy wybierzesz Zamknij i załaduj, wynik trafi do tabeli w arkuszu albo do modelu danych.
W następnym miesiącu umieść nowy eksport w tym samym miejscu i naciśnij Odśwież wszystko. Te same kroki wykonają się ponownie. Twój oryginalny plik nigdy się nie zmienia, bo Power Query tylko z niego czyta.
Zadania, z którymi radzi sobie dobrze
- Dołączanie: połącz dwanaście miesięcznych plików o tych samych kolumnach w jedną tabelę.
- Scalanie: dopasuj dwie tabele po wspólnej kolumnie. To trochę jak wyszukiwanie, ale wszystkie dopasowania trafiają do wyniku naraz.
- Anulowanie przestawienia: zamień szeroką tabelę z jedną kolumną na miesiąc w uporządkowaną listę z kolumnami Miesiąc i Wartość. Takie dane łatwiej podsumować.
- Czyszczenie tekstu: usuń zbędne spacje, popraw wielkość liter, podziel „Smith, Ann” na dwie kolumny.
Jeśli to samo czyszczenie robisz ręcznie więcej niż dwa razy, to prawdopodobnie zadanie dla Power Query.
Power Pivot: kilka tabel, jedno podsumowanie
Wyobraź sobie tabelę Orders z kolumną CustomerID i osobną tabelę Customers z regionem każdego klienta. Bez Power Pivot musiałbyś skopiować region do tabeli Orders za pomocą wyszukiwania. Z Power Pivot dodajesz obie tabele do Modelu danych i rysujesz relację między dwiema kolumnami CustomerID. Tabela przestawna może wtedy grupować zamówienia według regionu bezpośrednio.
Model danych przechowuje dane też w formie skompresowanej, więc może zawierać znacznie więcej wierszy niż limit 1,048,576 wierszy arkusza. Własne miary możesz pisać w języku formuł o nazwie DAX, na przykład sumę, która pomija anulowane zamówienia.
Wybór narzędzia i kilka ostrzeżeń
- Niespójne lub zmieniające się dane wejściowe: Power Query.
- Kilka powiązanych tabel, bardzo duże dane, wielokrotnie używane miary: Power Pivot (Model danych).
- Małe, uporządkowane dane używane jednorazowo: zwykłe formuły albo zwykła tabela przestawna w zupełności wystarczą.
- Dostępność funkcji zależy od wersji Excela i platformy. Okno Power Pivot w szczególności nie jest dostępne w każdej edycji, a Excel dla Maca ma mniej opcji niż Excel dla Windows.
Oba narzędzia dobrze ze sobą współpracują: oczyść dane w Power Query, załaduj je do Modelu danych, a potem połącz i podsumuj.