Zabawa danymi w Power Query, czyli koniec szukania – czas na łączenie
Power Query to taka funkcjonalność Excela, która zdecydowanie zmieniła pracę z tym narzędziem. No teraz to już jest taki Excel na wypasie i fajnie jest z tego „wypasu” w pełni korzystać. Jedną z takich możliwości jest zamienienie korzystania z funkcji „wyszukaj” na łączenie danych za pomocą zapytań. Brzmi skomplikowanie ale jest super proste i intuicyjne. To jak przesiadka z auta z klimą TWO – „two windows open” – na „prawdziwą” klimatyzację.
No ale po co to robić? Ponieważ jest:
- wygodniej,
- szybciej, (zwłaszcza przy dużych zbiorach danych),
- mniej błędogennie, możemy sobie szybko dodać arkusz walidacyjny z brakami w naszym słowniku,
- estetyczniej – nie musimy zagnieżdżać piętnastu „jeżeli – błąd”.
I wiadomka, najważniejsze, sława i podziw współpracowników, uścisk dłoni szefa i pierwszeństwo do robienia kawy w firmowym ekspresie. Reasumując – no warto!
Tylko jak to zrobić? Bardzo prosto, ale zamiast rozpisywać się w teorii to najlepiej będzie to pokazać na przykładzie.
Dodawanie tabel do POWER QUERY
Wyobraźmy sobie że mamy Excela (wooow!) z listą zakupów z całego tygodnia jak niżej.
Teraz to już w ogóle: brawa dla nas i trzy razy wooow!
Jeszcze nie ma tego dużo, ale nie jesteśmy już na studiach… W trakcie miesiąca będą dochodzić do listy kolejne produkty spożywcze. Fajnie byłoby mieć produkty podzielone na różne kategorie, żeby później zobaczyć ile wydajemy i czy mieścimy się w budżecie na poszczególne grupy produktów.
Część z tych produktów jest zupełnie powtarzalna, jak: chleb, benzyna, wino, mleko, czekolada. Byłoby zupełnie bez sensu przypisywać za każdym razem kategorie „z ręki”. Istniałoby też ryzyko błędnego przypisania kategorii. Zwłaszcza pod koniec miesiąca jak wino trafiłoby do jedzenia, bo stało się nam niezbędnym do życia elementem codziennej diety. Nie inne, jak bezalkoholowe. : )
Tworzymy więc słownik, który będzie wyglądać tak:
Teraz za pomocą Power Query połączymy te dane ze sobą. Na początek, z obydwu tych zakresów danych zrobimy tabele:
Zaznaczamy dane -> Wstążka „Wstawianie” -> „Tabela”:
Od razu zadbajmy o nasze tabele i nazwijmy je jakoś, bo każdy tabela lubi mieć nazwę. Pierwszą nazwiemy „Lista zakupów”, a drugą „Kategorie”
Teraz dodamy te tabele do Power Query. Można to zrobić na kilka sposób. Przykładowo tak:
Klikamy prawym przyciskiem myszy na tabelę -> wybieramy „Pobierz dane z tabeli/zakresu” -> dane automatycznie zostaną „przekazane” do Power Query.
Inną możliwością jest wejście we wstążkę „Dane” i skorzystanie z opcji w części „pobieranie i przekształcanie danych”:
Teraz przechodzimy do działania w ramach Power Query. Mamy widoczne dwa zapytania, każde z nich to jedna z naszych tabel w Excelu:
To co będziemy chcieli zrobić to każdemu produktowi z „Listy zakupów” przypisać „Kategorie” produktu. W kolejnym kroku będziemy chcieli zrobić dwa arkusze walidacyjne. Jeden z nowymi produktami, a drugi sprawdzający jakie produkty mamy w poszczególnych kategoriach.
Łączenie tabel w POWER QUERY – scalanie
Zaczniemy od połączenia informacji z dwóch zapytań. Aby to zrobić zaznaczamy dwa zapytania – wstążka w Power Query – „Narzędzia główne” -> sekcja „Połącz” -> „Scal Zapytania”. Ja wybieram „Scal jako nowe” ponieważ chciałabym, aby wynik tego połączenia był nowym zapytaniem:
Wybieramy, które zapytania będziemy ze sobą łączyć. Chcemy, aby do listy zakupów (wybieramy to zapytanie jako pierwsze) była dodana informacja o kategorii produktów (wybieramy to zapytanie jako drugie):
Teraz potrzebujemy wspólnego mianownika z obydwu tabel. Takiego czegoś, co umożliwi łączenie tabel w POWER QUERY i powie naszemu wypasionemu excelowi: „O patrz Excelu – to jest ta samo, po tym dopasowuj dane z poszczególnych tabel”. U nas to będzie nazwa produktu:
A potem wybieramy sposób tego łączenia. Mamy do wyboru kilka opcji:
Wybieramy tę pierwszą, bo znowu chcemy żeby Power Query zachowało nam wszystkie wiersze z pierwszej tabeli – z listy produktów – i dodało do każdego produktu pasującą kategorię z drugiej tabeli.
Popijając w pełnym spokoju łyk herbaty klikamy „OK”!
Pojawiło nam się nowe zapytanie automatycznie nazwane przez Power Query jako „Scalanie1”. Wygląda to już prawie wygląda tak, jakbyśmy chcieli, no ale jeszcze nie do końca. Pierwsze co zrobimy to zmienimy mu nazwę na jakąś bardziej… no pasującą do zawartości. Niech będzie „Produkty_Kategorie” (prawy przycisk myszy na zapytaniu -> „Zmień nazwę”).
Teraz musimy rozwinąć dane z dołączonej tabeli. Aby to zrobić, klikamy na ikonę z dwoma strzałkami przy kolumnie „Kategorie”, a następnie wybieramy opcje „Rozwiń”. Zaznaczamy tylko „Kategorie” i klikamy „OK”.
No i czary mary, excelowe hokus pokus, mamy dodane kategorie.
Teraz wystarczy już tylko kliknąć na „Zamknij i załaduj”, no i mamy arkusz z naszą nową tabelą z dodanymi kategoriami:
Przy aktualizacji wystarczy, że będziemy dodawać dane do listy zakupów, a kategorie będą się nam przypisywać automatycznie w PQ.
Łączenie tabel w POWER QUERY – dodanie nowych kategorii
Teraz dodamy trzy nowe produkty do naszej listy zakupów, z tym że jeden produkt będzie kompletnie nowy:
I odświeżymy nasze zapytanie:
Nasze świąteczne Pomarańcze mają pustą kategorie, bo nie mieliśmy do tej pory pomarańczy w słowniku. Możemy zawsze kupować to samo, ale szybko znudzi nam się jedzenie chleba i popijanie winem. No chyba że jesteśmy na studiach, to inna historia. Ale jak ustaliliśmy wcześniej – ten etap życia jest już za nami. Jak wśród nowych produktów będzie tylko jeden no to spoko, ale jakby ich było kilka to już mniej fajnie. Dlatego zrobimy sobie szybkie zapytanie walidacyjne, które będzie nam „wyrzucać” tylko te produkty, które nie mają kategorii, abyśmy mogli szybko zaktualizować sobie słownik. Zaczniemy od zduplikowania zapytania „Produkty_Kategorie”:
Od razu zmienimy sobie nazwę tego nowego zapytania – zapytania, tak jak tabele, lubią mieć nazwę, która opisuje co się w nich znajduje:
Jak walidować dane za pomocą Power Query?
Chcemy, aby w wyniku zapytania zostały tylko te produkty, które w kategorii mają „null” (czyli nie mają przypisanej żadnej kategorii). W tym celu wyfiltrujemy po prostu „null” w kolumnie kategoria:
Następnie, aby produkt nam się pokazywał tylko jeden raz (np. gdybyśmy mieli pomarańcze dwa razy na liście bo są święta) skorzystamy jeszcze z opcji „usuń duplikaty”:
Na koniec usuniemy kolumnę z ceną, bo nie będzie nam potrzebna do zaktualizowania słownika. Efekt będzie taki:
Klikamy na zamknij i załaduj. Od teraz w osobnym arkuszu będą nam się pojawiać artykuły bez Kategorii. Teraz możemy dodać nasze pomarańcze do słownika i odświeżyć pozostałe zapytania.
Załóżmy że raz na jakiś czas chcemy mieć przegląd produktów w poszczególnych kategoriach. Aby to sprawdzać, przykładowo, możemy dodać arkusze walidacyjne, które będą się odświeżać i pokazywać nam jakie produkty mamy w której kategorii. Tym razem zduplikujemy zapytanie „Kategorie”:
Zaczniemy od pogrupowania wierszy – tak, aby Power Query zrobiło nam listę z produktów w poszczególnych kategoriach: Wstążka „Narzędzia główne” -> Grupowanie według -> wyskoczy nam dodatkowe okienko -> w polu „Operacje” wybieramy opcje „wszystkie wiersze”:
Teraz będziemy chcieli żeby każda kategoria to była kolumna z wypisanymi produktami, a więc zamienimy układ kolumn i wierszy – dodamy kolumnę przestawną z Kategorii: zaznaczamy kolumnę Kategoria -> wstążka „przekształć” -> „kolumna przestawna”:
Następnie każdą z kategorii będziemy chcieli mieć jako osobne zapytanie, dla przykładu -> produkty w kategorii „Jedzenie” jako listę produktów, które są przypisane jako jedzenie. W tym celu zduplikujemy zapytanie, które właśnie stworzyliśmy, usuniemy z niego inne kolumny niż „Jedzenie” i rozwiniemy wiersze w tabeli:
Ta dam, no i jest:
Szybki wniosek – niby nie jesteśmy już na studiach, ale nabiał, kurczak, chleb i pomarańcze od święta to nie brzmi jak najzdrowsza dieta 😉 Możemy teraz zastanowić się nad swoją dietę, a między czasie powtórzyć to działanie na pozostałych kategoriach produktów.
Podsumowanie
Możliwości „zabawy” danymi w PowerQuery jest naprawdę dużo. Powyżej widzicie jeden ze sposobów na usprawnienie sobie pracy bez oglądania „endeków” i sprawdzania „dolarów” w wyszukaj pionowo 😊 Wystarczy tak naprawdę zadać sobie pytanie „to co bym jeszcze chciał/a zobaczyć jak już będę mieć te dane” i pokombinować jak to zrobić. A po inspiracje… zawsze można zajrzeć na bloga 😊
Agnieszka Kmieć
