Co to jest Power Query Excela?
Power Query Excela to dodatek, który miał premierę w 2013 roku. Służy do pobierania i przekształcania danych. Pomyślisz: „Hmm, ale przecież mogę się odwołać do danych z innego pliku formułą. Po co mi Power Query?”.
Sęk w tym, że narzędzie, o którym rozmawiamy, pozwala wpinać się nie tylko do innych plików Excela czy plików csv. Za jego pomocą możemy pobierać dane z całych folderów, ze stron www, baz danych SQL, a w wersji abonamnetowej Excela nawet z plików PDF!
Znakomitą większość rzeczy w Power Query jesteś w stanie wyklikać ze wstążki. Są one zapisywane w języku M, ale jego znajomość nie jest konieczna, aby skutecznie pracować w tym narzędziu.
Najlepsze w Power Query jest to, że nie musisz łączyć się z plikiem źródła za każdym razem. Zrobisz to raz, a gdy przyjdzie czas aktualizacji danych, podmienisz plik źródła na nowy i odświeżysz jednym skrótem (CTRL+ALT+F5) wszystkie zapytania – raport się zaktualizuje.
Gdzie znaleźć Power Query Excela?
Aby móc skorzystać z dodatku Power Query musisz dysponować Excelem 2010 lub nowszym. Dla wersji 2010 i 2013 będzie potrzebne doinstalowanie dodatku. Możesz to zrobić bezpłatnie na stronie Microsoftu (tutaj Power Query dla Excela 2010 i 2013).
Jeśli chodzi o Excele 2016 i nowsze, Power Query jest już ich częścią i znajdziesz go na karcie DANE.
Jak połączyć się przy użyciu Power Query Excela ze źródłami?
Wybierz kartę DANE, a następnie źródło, z którego chcesz skorzystać (poprzedni zrzut ekranu). Dla przykładu weźmiemy stronę www Narodowego Banku Polskiego (kliknij tu).
Wybieramy opcję pobierania danych ze strony www.
Naszym oczom ukazuje się pole do wklejenia adresu, z którego dane mają być zaciągnięte. Wstaw tam adres strony.
Po kliknięciu OK Excel połączył się z www i przeszukał ją pod kontem obecności tabel. Skąd wie, że na stronie jakieś są? Strona NBP jest napisane w HTML. To język pisania stron. Są w nim znaczniki kodu, wskazujące tabele na stronie. Gdy ich nie ma, też da się wyciągnąć dane. To dłuższy temat, więc znajdziesz go w jednej z lekcji kursu o Power Query, Pivot i BI (link tutaj). Naszym oczom ukazuje się okno, w którym możemy wybrać interesującą nas tabelę ze strony. Wybieramy tę, w której znajdziemy kursy walut.
Finalny efekt importy walut w Power Query Excela
Pozostaje wybrać Załaduj albo Przekształć. Załaduj spowoduje wrzucenie tabeli do arkusza. Przekształcenie umożliwi nam na przykład wyfiltrowanie tylko tych kursów, które nas interesują.
Utworzenie takiego połączenia powoduje, że po wciśnięciu skrótu CTRL+ALT+F5 zapytanie się odświeży. Co to znaczy? Excel znowu poprosi stronę NBP o podanie aktualnych kursów i jeśli coś się zmieniło, nowe dane się pojawią. Nie musisz zatem łączyć się od nowa za każdym razem. Wystarczy jeden skrót!
Przekształcanie danych w Power Query Excela
Przykład z poprzedniego akapitu był naprawdę podstawowy. Pobieranie to dopiero początek. Gdy mamy już interesujące nas dane, możemy doprowadzić je do stanu używalności.
Problem z trudnym układem danych
Ile to razy system wyrzuca dane w nieprzyjemnym formacie? Ile razy klient lub dostawca wysyła nam plik, którego analiza to katorga, bo nie ma układu tabelarycznego?
W takich sytuacjach Power Query to złoto. Możemy raz stworzyć sekwencje kroków, które za każdym razem będą działać na plik w tym „trudnym układzie”. Sekwencja kroków nazywana jest zapytaniem. Gdy kolejny raz dostaniemy taki plik, wystarczy wskazać nowe źródło dla zapytania i gotowe.
Problem z formatowanie dat i polskich znaków
Zdarzyło Ci się otrzymać plik, w którym daty nie działały? Nie dało się na nich filtrować, nie dało się na nich wykonywać obliczeń? A może zdarzyło Ci się trafić na plik, po którego otwarciu zamiast polskich znaków, ukazały Ci się krzaczki niczym pismo klinowe?
W obu przypadkach świetnie sprawdzi się Power Query. Korzystając z przekształcenia danych możesz łatwo wybrać format daty lub regionu, z którego otrzymujesz dane, aby format się naprawił.
Zacinający się plik lub zbyt dużo danych w pliku CSV
Gdy pracowałem jeszcze na etacie, mieliśmy plik z zapasem na stanie i w drodze. Plik regularnie przyrastał, otwierał się coraz dłużej, aż pewnego dnia przekroczył limit wierszy w Excelu, co powodowało, że nie mogliśmy go otworzyć za pomocą tego programu. W tamtych czasach nie wiedzieliśmy o istnieniu Power Query, więc jeden z pracowników wymyślił makro Excela, które przy użyciu języka SQL (tutaj kurs SQL od podstaw) i Accessa wyciągało część danych z dużego pliku tak, aby było ich mniej niż limit 1 048 576 arkusza. Wszystko pięknie tyle, że do stworzenia czegoś takiego potrzebna była wiedzą z 4 obszarów: Excela, VBA, SQLa i Accessa i przynajmniej dzień pracy, żeby, zrobić napisać rozwiązanie oraz sprawdzić czy działa.
Jakie jest rozwiązanie?
Gdybyśmy wtedy znali Power Query, mogliśmy to zrobić w mniej niż 10 minut. Power Query pozwala pracować w Excelu na znacznie powyżej miliona wierszy. Oczywiście nie załadujesz wszystkich danych do arkusza, ale nie jest to potrzebne. Mając Power Query, możesz utworzyć połączenie do pliku źródłowego, wybrać tylko te dane, które są Ci potrzebne (np. wybrane kategorie, faktury za konkretny rok czy zamówienia z określonego przedziału) i to je załadować do arkusza, co również nie jest konieczne.
Jeśli zdeponujesz swoje dane w tzw. modelu danych, będziesz mógł podsumowywać je przy użyciu Power Pivot (Tabeli Przestawnej na sterydach), w zasadzie nie widać fizycznie tych milionów wierszy w Twoim pliku. Przewag takiego rozwiązania jest wiele:
- zawsze odnosisz się do tego samego pliku źródła, więc w przypadku jego aktualizacji, nic Ci nie umknie
- nie ma limitu około 1 mln wierszy
- plik działa szybko, bo nie masz w nich takiej liczby danych, a nadal masz do nich dostęp
- przy pomocy tabeli przestawnej możesz patrzeć na bardzo duże zestawy danych pod różnym kątem, a także łączyć ze sobą dane z wielu źródeł.
Do czego może przydać się Power Query Excela?
Kilka przypadków padło już w tym wpisie. Jednym z nich było pobieranie kursów walut ze strony NBP „na żywo”, ale to nie wszystko.
Zepsuty plik Excela
Czasami dostaję pytania w stylu „nie mogę otworzyć pliku Excela, a są mi potrzebne dane, które się w nim znajdują” albo „ten plik chodzi strasznie ociężale i muszę czekać po minucie, żeby przełączyć w nim arkusz”. W obu przypadkach na białym koniu wjeżdża Power Query Excela. Możesz skorzystać z odwołania się do pliku Excela bez otwierania go, co znacznie zwiększa szansę na bezbolesne dostanie się do pliku.
„Zapivotowane” dane
Pewnie zdarzyło Ci się dostać plik, w którym dane ewidentnie są kopiuj wklej z analizy tabelą przestawną i przez ich układ analiza staje się uciążliwa.
Power Query umożliwia anulowanie przestawienia kolumn, co w mniej niż minutę odwraca działanie tabeli przestawnej. Wystarczy wybrać obszar danych do odpivotowania i kliknąć z karty DANE opcję Z tabeli/zakresu.
Kolejno zaznacz kolumny do odpivotowania i wybierz „Anuluj przestawnie kolumn”.
To jedno kliknięcie, a następnie załadowanie danych do arkusza powoduje, że praca na danych staje się znacznie łatwiejsza i już możesz dużo łatwiej analizować otrzymane informacje czy to formułami, czy tabelami przestawnymi.
To oczywiście tylko kilka dodatkowych przykładów do całego wpisu, więcej zastosowanie znajdziesz kontynuując czytanie tego wpisu.
Dlaczego Power Query Excela to największa zmiana od lat?
Danych z roku na rok przybywa. Limit 1 048 576 wierszy w jednym arkuszu Excela jest coraz bardziej uciążliwy. Nawet jeśli się go nie osiągnie, praca formułami na chociażby 100 000 wierszy staje się już bardzo czasochłonna. Przeliczenie kilkuset formuł trwa niejednokrotnie i około minuty. Wyobraź sobie ile tych minut tracą wszyscy użytkownicy Excela na świecie w skali dnia. Z pomocą przychodzi Power Query. Możesz w nich dokonywać zmian znacznie szybciej, niż przy użyciu wbudowanych formuł. Przydaje się też świetnie do cyklicznych zadań. Raz zbudowane zapytanie może być przez Ciebie szybko stosowane wielokrotnie na każdej, nowej wersji danych źródłowych. To olbrzymia różnica w tempie i możliwościach pracy w Excelu. Największa od lat.
Dlaczego warto uczyć się Power Query Excela?
Efektywność, szybkość, łatwość, wyróżnienie się wśród używających Excela to tylko niektóre argumenty, przemawiające za nauką pracy w Power Query. Są zadania, których nie wykonasz niczym inny poza tym narzędziem. Jeden z przykładów podałem nieco wcześniej. Zobacz ile kompetencji musiał posiadać kolega z mojego zespołu, aby poradzić sobie z tym zadaniem. Dodam, że ten człowiek miał około 45 lat i 20 lat doświadczenia w pracy jako analityk bankowy. W tym konkretnym przypadku można było jego całodniowy wysiłek zastąpić w 10 minut.
Power Query jest z nami od roku 2013 i nadal niestety nie wszędzie jest stosowane. Możesz być tym, kto pokaże w firmie czym jest to narzędzie, co wpłynie pozytywnie na postrzeganie Cię przez zespół i przełożonych – w końcu sprawiasz, że kolegom i firmie jest łatwiej. Możesz zabłysnąć na rozmowie o pracę, rozwiązując zadania w sposób, którego żaden z kandydatów przed Tobą nie użył.
Analiza Business Intelligence a Power Query Excela
Power Query to fundamentalne narzędzie w analizie BI (Business Intelligence). Na popularności zyskują narzędzia jak Tableau czy Power BI. To drugi jest wydawane przez Microsoft i bazuje na dokładnie tym samym silniku, co Power Query i Power Pivot Excela, ale o tym nieco więcej w kolejnym akapicie. Ważne dla Ciebie jest to, że umiejąc działa w Power Query i Pivot wystarczy, że oklikasz się nieco w Power BI i możesz dopisać do CV również umiejętność obsługi narzędzia BI!
Czym różni się Power Query Excela od Power Query BI?
Power Query Excela i Power Query z Power BI to… to samo narzędzie. Nie brzmi zbyt prawdopodobnie? Jeśli dysponujesz narzędzie Power BI (w kursie mówię, jak zainstalować je bezpłatnie) możesz zrobić prosty test. Stwórz zapytanie w Power Query Excela. Możesz nawet użyć przykładu ze stroną NBP, opisaną w tym poście. Skopiuj następnie zapytanie i wklej do Power Query narzędzia Power BI. Okazuje się, że możesz wykonać taką operację! Właśnie dlatego w moim kursie uczymy się pracy z Power Query i Power Pivot w Excelu — bo działają analogicznie do narzędzi w środowisku BI. Nie czekaj i razem odczarujmy dla Ciebie te narzędzia, wprowadzając Twoje analizy w XXI wiek — kliknij tu.
Owocnej nauki Power Query, Pivot i BI,
Michał Kowalczyk
