fbpx

POWER QUERY, czyli jak zyskać masę czasu

POWER QUERY, czyli jak zyskać masę czasu

POWER QUERY (PQ), wraz z POWER PIVOT i POWER VIEW to prawdopodobnie najbardziej rewolucyjne narzędzie od czasu tabel przestawnych. Dlaczego? Ponieważ daje Ci, prosty sposób na: pobranie danych z wielu różnych źródeł, przekształcenie ich w pożądany sposób i wysłanie do arkusza, gdzie możesz je dalej analizować.

To co czyni to narzędzie tak potężnym jest fakt, że dzięki niemu możesz robić rzeczy, które wcześniej często były zarezerwowane dla zaawansowanych użytkowników VBA. Co więcej jest to bardzo proste. Podstawową obsługę POWER QUERY „ogarniesz” w kilka godzin.

Power Query - jak zyskać masę czasu. Mężczyzna trzymający zegar.

Co to jest POWER QUERY?

Fachowa definicja powie, że POWER QUERY to tzw. narzędzie ETL (z ang. Extract, Transform, Load – czyli wyciągnij, przekształć i załaduj)

Jeżeli mam to przełożyć na bardziej praktyczny i prosty język…posłużę się przykładem. Wyobraź sobie, że musisz zrobić analizę danych sprzedażowych. Dane źródłowe o dziennych wartościach sprzedaży są dostępne na stronie www, informacje o klientach masz w bazie Access, a szczegółowy opis produktów w dziesiątkach plików Excel.

Żeby było ciekawiej wszystkie te dane są w różnym układzie, często zawierają nadmiarowe spacje i wiersze. Słowem jeden wielki chaos. Znasz to?

PQ, pozwoli Ci pobrać dane z tych źródeł (Extract) Następnie oczyścisz je z nadmiarowych informacji i uporządkujesz w pożądaną strukturę (Transform) To, z kolei pozwoli Ci wysłać jedną lub wiele tabel do dalszej analizy, np. za pomocą POWER PIVOT (Load)

Co więcej raz wykonana praca, nie będzie musiała być powtarzana. Gdy kolega zapisze kolejny plik z opisem kolejnego produktu – PQ będzie wiedział, co ma z nim zrobić i niejako automatycznie dołączy do pierwotnego zestawu danych. Prawda, że piękne? 😉

Gdzie ja mam POWER QUERY?

Myślę, że zachęciłem Cię do dalszego zgłębiania tajników tego dodatku. No właśnie – dodatku, bo dla Excela 2010 i 2013 POWER QUERY jest dostępny w formie dodatku, który instalujesz jak każdy program. W wersji 2016 PQ jest już „wbudowany” w podstawową wersję.

Po zainstalowaniu, w wersji 2010 / 2013, na wstążce zobaczysz nową kartę – POWER QUERY. Ale nie w wersji 2016. Ta funkcjonalność została włączona do karty DANE jako POBIERANIE I PRZEKSZTAŁCANIE DANYCH. Dalszy opis dotyczy dodatku w wersji 2010 / 2013 ale nie powinieneś mieć kłopotu z odczytaniem o czym piszę, jeżeli dysponujesz nowszą wersją.

Automatyczna aktualizacja danych

Na początku wspomniałem, że za pomocą PQ możesz pobrać dane z wielu źródeł. Możliwości jest naprawdę mnóstwo. Zobacz sam:

Power Query - pobieranie danych z wielu różnych źródeł.

Zakładam, że to Twoja pierwsza styczność z PQ dlatego na potrzeby artykułu przejdziemy przez dość prosty przykład. Powiedzmy, że każdego dnia koledzy z trzech różnych regionów przesyłają Ci dzienne zestawienia sprzedaży. Do tej pory ręcznie kopiowałeś dane z tych plików do swojej zbiorczej bazy, gdzie wykonywałeś dalsze analizy.

Dzięki PQ to samo osiągniesz automatycznie. Oznacza to, że każdego dnia, po otrzymaniu danych będziesz musiał zrobić tylko dwie operacje:

  • zapisać je w odpowiednim miejscu (folderze)
  • kliknąć przycisk odśwież 😉

Pobierz dane

Przećwiczmy to razem. Pobierz te dane i zapisz je w dowolnym folderze na dysku. Następnie utwórz nowy zeszyt (plik Excela) i przejdź na kartę POWER QUERY (wersja 2010/2013) lub DANE (w późniejszych wersjach). Tam odnajdujemy polecenie, które znajduje się w sekcji POBIERZ DANE ZEWNĘTRZNE > Z PLIKU > Z FOLDERU.

W pierwszym kroku, będziesz musiał wskazać ścieżkę do folderu, w którym zapisałeś dane pochodzące z regionów. Zobaczysz okienko, które będzie przedstawiało zawartość folderu z kilkoma dodatkowymi informacjami. Następnie kliknij w przycisk EDYTUJ. Tutaj mała ale ważna dygresja.

Na tym etapie pojawił się edytor zapytań. Edytor rejestruje wszystkie Twoje kroki i pozwala na je późniejsze odtworzenie, usunięcie jak i edycję. Możesz też samodzielnie konstruować zapytania lub edytować istniejące za pomocą języka M. Oczywiście to trudniejsze ale daje pełną kontrolę i elastyczność. Jeżeli tworzyłeś kiedyś makra, to trochę przypomina to właśnie rejestrację makr vs. pisanie kodu VBA.

Interesujące nas dane znajdują się w pierwszej kolumnie CONTENT. Z pewnością zauważyłeś ikonę z dwoma małymi strzałkami, które wskazują na możliwość rozwinięcia zawartości. Zanim to zrobimy, pozbędziemy się reszty kolumn. Nie będą nam dalej potrzebne.

Wybierz kolumnę CONTENT, a następnie kliknij prawym przyciskiem myszy i wybierz polecenie USUŃ POZOSTAŁE KOLUMNY. Teraz możesz już kliknąć w ikonę ze strzałkami. Pojawi się kolejne okno dialogowe, gdzie będzie odzwierciedlona struktura przykładowego pliku z danymi. Ponieważ każdy plik z danymi z regionów, zawierał tabelę (TABELA 1) – wybierzemy właśnie ją jako źródło danych.

Po chwili zobaczysz wszystkie dane (z 3 plików) połączone w oknie edytora. Możesz się o tym przekonać klikając w filtr na kolumnie region i wybierając ZAŁADUJ WIĘCEJ.

Na pewno zauważyłeś, że wszystkie dotychczasowe kroki były rejestrowane w kolumnie po prawej stronie ekranu. To jedna z wielu korzyści jakie niesie ze sobą PQ. Możesz cofać się do dowolnego kroku, modyfikować go czy usuwać. Ale przede wszystkim PQ zapamiętuje wszystkie operacje i jest w stanie je odtworzyć, jeżeli do folderu dodasz kolejne pliki. Ale o tym na końcu.

Oczyść i przekształć dane

Załóżmy, że aby nasze połączone dane były jeszcze bardziej przydatne do analizy potrzebujemy zrobić jeszcze trzy rzeczy:

  1. chcemy aby nazwa klienta była pisana wielkimi literami,
  2. nie potrzebujemy kolumn „Przedstawiciel Id” i „Klient Id”,
  3. chcielibyśmy mieć dodatkową kolumnę, która pokażą nam tylko miesiąc sprzedaży.

Przekształć na wielkie litery

Kliknij w kolumnę „Klient”, a następnie z karty PRZEKSZTAŁĆ wybierz polecenie FORMAT > WIELKIE LITERY.

Usuwanie niepotrzebnych kolumn

Zaznacz kolumny „Przedstawiciel Id” i „Klient Id”, a następnie kliknij prawym przyciskiem myszy i wybierz polecenie USUŃ KOLUMNY.

Dodaj nową kolumnę

Chcemy mieć miesiąc sprzedaży w osobnej kolumnie i uzyskamy go z kolumny „Data”. Pokażę Ci sposób, który umożliwia później dowolną zmianę nazwy nagłówka tej kolumny. Kliknij na kolumnę „Data”, a później wybierz polecenie DUPLIKUJ KOLUMNĘ z karty DODAJ KOLUMNĘ (to samo polecenie znajdziesz w menu kontekstowym)

Jak sama nazwa polecenia wskazywała utworzyłeś duplikat kolumny „Data”. Teraz na karcie PRZEKSZTAŁĆ wybierz polecenie DATA > MIESIĄC > MIESIĄC. Dodatkowo możesz zmienić nazwę tej kolumny klikając dwukrotnie na nazwę nagłówka i wpisując np. „Miesiąc sprzedaży”.

Załaduj do Excela

Ostatnia faza działań to załadowanie pobranych i oczyszczonych danych z powrotem do arkusza. Możesz to zrobić przesyłając dane bezpośrednio do tabeli danych lub wybierając polecenie UTWÓRZ TYLKO POŁĄCZENIE.

To spowoduje, że dane nie będą załadowane bezpośrednio do arkusza. Powstanie natomiast połączenie, które będziesz mógł później wykorzystać np. podając je w źródle do tabeli przestawnej.

Na powyższym filmie zobaczysz komplet opisanych działań i przykładową tabelę przestawną, która powstała na bazie wcześniej przygotowanych danych.

Jeżeli powyższe ćwiczenie nie przekonało Cię do Power Query, to spróbuj zrobić jeszcze jedną rzecz. Zapisz i zamknij plik na którym pracowałeś. Następnie przejdź do dowolnego pliku z danymi źródłowymi i dodaj kolejne rekordy albo zmodyfikuj istniejące. Zamknij i zapisz, a następnie ponownie otwórz plik, w którym przygotowywałeś zapytania PQ.

Wybierz polecenie ODŚWIEŻ, z karty DANE. Jak widzisz wszystko zostało odświeżone! Co więcej, gdy dojdzie kolejny region i umieścisz w folderze źródłowym plik w takim samym formacie jak poprzednie – zadzieje się dokładnie to samo. Dane z nowego regionu zostaną automatycznie zaczytane! Powiedz sam, czy to nie magia? 😉


Zapisz się już teraz na kurs online EXCELNESS ADEPT


Podsumowanie

Ten artykuł to tylko namiastka tego co potrafi Power Query. Mam jednak nadzieję, że zachęciłem Cię na tyle iż podobnie jak ja staniesz się wielkim entuzjastą Power Query. W połączeniu z Power Pivot, dostajesz absolutnie potężne narzędzia do pracy i analizy na dużej ilości, niekoniecznie jednorodnych danych. Daj proszę znać w komentarzu, czy używałeś już PQ i jakie są Twoje doświadczenia.


Masz inne problemy z Excelem? Zapraszam Cię na grupę – Keep Calm And Use Excel na Facebooku. To miejsce, w którym pomagamy sobie nawzajem w codziennych wyzwaniach z Excelem 😉

Podobał Ci się artykuł? Podziel się z innymi

2 thoughts on “POWER QUERY, czyli jak zyskać masę czasu

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *

Facebook
Facebook
Instagram
YouTube
RSS