Minuty czytania: 7

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.

Dłonie łączące puzzle

Dodawanie tabel do POWER QUERY

Wyobraźmy sobie że mamy Excela (wooow!) z listą zakupów z całego tygodnia jak niżej.

Tabela Excel z listą zakupów

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:

Tabela produktów z przypisaną kategorią

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”:

Tabela strukturalna z listą zakupów

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”

Nazywanie tabeli strukturalnej

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.

Pobieranie danych z tabeli 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”:

Menu "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:

Widok zapytań w Power Query

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:

Łączenie tabel w POWER QUERY

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):

Łączenie tabel w POWER QUERY - scalanie

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:

Łączenie tabel w POWER QUERY - scalanie na podstawie wspólnego identyfikatora

A potem wybieramy sposób tego łączenia. Mamy do wyboru kilka opcji:

Łączenie tabel w POWER QUERY - scalanie "Lewe zewnętrzne"

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ę”).

Łączenie tabel w POWER QUERY - scalone tabele

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”.

Łączenie tabel w POWER QUERY - scalone tabele - rozwijanie elementów

No i czary mary, excelowe hokus pokus, mamy dodane kategorie.

Łączenie tabel w POWER QUERY - scalone tabele - efekt końcowy

Teraz wystarczy już tylko kliknąć na „Zamknij i załaduj”, no i mamy arkusz z naszą nową tabelą z dodanymi kategoriami:

Zamknij i załaduj w POWER QUERY
Finalny widok scalonych tabel

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:

Dodawanie nowych produktów do listy zakupów

I odświeżymy nasze zapytanie:

Odświeżanie zapytania POWER QUERY
Odświeżanie zapytania POWER QUERY - efekt końcowy

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”:

Duplikowanie zapytania w POWER QUERY

Od razu zmienimy sobie nazwę tego nowego zapytania – zapytania, tak jak tabele, lubią mieć nazwę, która opisuje co się w nich znajduje:

Nazywanie zapytania w POWER QUERY

.

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:

Filtrowanie tabeli w POWER QUERY

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”:

Usuwanie duplikatów w w POWER QUERY

Na koniec usuniemy kolumnę z ceną, bo nie będzie nam potrzebna do zaktualizowania słownika. Efekt będzie taki:

Walidacja danych w POWER QUERY

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”:

Duplikowanie zapytania "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”:

Grupowanie w POWER QUERY

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”:

Kolumna przestawna w POWER QUERY

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:

Duplikowanie nowego zapytania
Usuwanie innych kolumn w POWER QUERY
Rozwijanie danych w tabeli

Ta dam, no i jest:

Efekt końcowy w tabeli walidacyjnej

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ć



0 Comments

Dodaj komentarz

Avatar placeholder

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *