Kategoria: Power Query

O czym dzisiaj będzie?

Dzisiejszy wpis poświęcony jest wyzwaniem z jakim wielu użytkowników Excel wzmaga się na co dzień. Problemem tym jest łączenie danych z różnych zbiorów danych (tabel).

Jeżeli nie wiesz czym jest Power Query i/lub nie wiesz gdzie znajdziesz przyciski do tworzenia zapytań serdecznie zapraszam Cię do poprzedniego wpisu tutaj.

Zapewne nieraz spotkałeś czy spotkałaś się z taką sytuacją, w której była potrzeba połączenia różnych zbiorów danych za pomocą jakiegoś klucza. W poprzednim wpisie opisywałem jak pobierać i łączyć różne pliki w o tej samej strukturze z jednego katalogu. Tym razem wyzwanie jest nieco inne.

Musimy za pomocą klucza połączyć jedne dane z drugimi. Taką operację można porównać do klauzuli JOIN stosowanych w języku SQL.

Oczywiście, często z powodzeniem możemy takie zadanie wykonać za pomocą wbudowanych funkcji Excel najczęściej z kategorii funkcji wyszukujących na przykład wyszukaj pionowo albo za pomocą VBA.

Łączenie danych za pomocą formuł Excel

Natomiast w pierwszym rozwiązaniu problemem może być wydajność arkusza ze względu na ilość formuł wykorzystania do tego zadania, gdyż te przeliczają się za każdym razem, gdy podejmujemy próbę edycji komórki. Co może spowodować, że te same dane są przeliczane wielokrotnie bez znaczących zmian danych. Jeżeli jednak dane często się zmieniają nawet podczas korzystania ze skoroszytu warto pomyśleć nad takim rozwiązaniem, ale to temat na zupełnie inny wpis.

Łączenie danych za pomocą VBA

Jeśli chodzi o VBA problemem może okazać się bariera znajomości VBA w stopniu średnio zaawansowanym, gdyż wykonane przez nas operacje nawet jak zadziałają mogę wydać się mało zoptymalizowane co skutkuje tak jak w poprzednim przykładzie niską wydajnością. Natomiast czasami i takie rozwiązanie ma swoje uzasadnienie na przykład w momencie, gdy chcemy w czasie rzeczywistym zmieniać dane na podstawie chociażby zmiany parametrów. W Power Query trzeba każdorazowo odświeżać zapytanie.

Zadanie do wykonania

Przykład wykorzystania takiego łączenia danych przedstawiono poniżej. Pierwsza tabela mogłaby dla tego przykłądu wyglądać jak ta poniżej.

Z punktu widzenia działania zapytań Power Query nie ma znaczenia ilość wierszy ani ilość kolumn danych w tabeli. Zarówno mogłoby to być 4 wiersze i 3 kolumny, jak i pół miliona wierszy i 50 kolumn. W przedstawionej powyżej tabeli widzimy w pierwszych wierszach nagłówki kolumn dotyczących przykładowych ID klienta, Imię sprzedawcy oraz ich województwo. W tym skoroszycie mamy również drugą tabelę wyglądającą jak ta poniżej.

Jak można zauważyć zarówno w tabeli pierwszej, jak i drugiej wspólną kolumną jest ID_Klienta. Oznacza to, że każdy wiersz z numerem klienta odpowiada oznaczeniom z tabeli pierwszej. W power Query nie ma znaczenia czy łączymy tabele za pomocą kolumny liczbowej, jak i kolumny tekstowej, równie dobrze w tabeli drugiej mógłby znajdować się imię Sprzedawcy i wtedy łączylibyśmy kolumny po nazwie klienta, a nie po jego numerze. Tak, czy inaczej chcielibyśmy w nowym zapytaniu połączyć dane ze sobą by w tabeli drugiej uzupełnić informacje o brakujące informacje.

Importowanie danych do zapytania Power Query z tego samego skoroszytu

Żeby zaimportować tabelę do zapytań Power Query należy zaznaczyć komórkę będąca częścią tabeli oraz wejść na kartę Dane i z sekcji Pobieranie i przekształcanie wybrać polecenie z tabeli/zakresu

Po wybraniu polecenia narzędzie zaznaczy wszystkie przylegające do siebie komórki względem komórki zaznaczonej tworząc jeden zakres danych i w niewielkim edytorze tworzenia tabeli umieści ten zakres w poluPrzekształcenie zakresu danych w tabeli konieczne jest, żeby utworzyć zapytanie w Power Query. Więcej korzyści wynikających z przekształceniem zakresu na tabelę znajdziesz w jednym z kolejnych wpisów.

Przekształcenie zakresu danych w tabeli konieczne jest, żeby utworzyć zapytanie w Power Query. Więcej korzyści wynikających z przekształceniem zakresu na tabelę znajdziesz w jednym z kolejnych wpisów.

Prawie na pewno zakres jest poprawny jednak przed naciśnięciem przycisku Ok upewnij się, że Excel zaznaczył go poprawnie, jeśli nie zmodyfikuj go wpisując zakres ręcznie lub korzystając z drzewa po prawej wybierz zakres myszką. Po wybraniu/upewnieniu się co do zaznaczonego zakresu i kliknięciu przycisku Ok

Po zatwierdzeniu ujrzysz okienko przedstawione poniżej. Póki co nie będziemy się skupiać nad tym zapytaniem, dlatego jedynie je sobie zaczytamy do listy zapytań tego skoroszytu klikając Zamknij i załaduj w prawym dolnym rogu i wybierzmy „Zamknij i załaduj do…” a następnie „Utwórz tylko połączenie„

Zróbmy analogicznie tak samo dla drugiej tabeli

Po wgraniu zarówno jednej tabeli, jak i drugiej do połączeń Power Query obecnego skoroszytu możemy taką listę zobaczyć wchodząc w kartę Dane i z sekcji „Zapytania i połączenia” wybrać „Zapytania i połączenia” pojawi się okna po prawej stronie z listą utworzonych przed chwilą połączeń.

Mając nasze dwa połączenia składające się z dwóch tabel możemy pokusić się o ich połączenia. W tym celu wejdźmy na jedno z nich klikając dwukrotnie myszką i z narzędzi głównych okna Power Query z sekcji Połącz wybierzmy opcję scal zapytania i z rozwijanej listy wybierzmy „scal zapytanie jako nowe„

W nowo otwartym oknie połącz dwie tabele według wspólnej kolumny jaką jest ID_Klienta tak jak to przedstawiono na obrazku poniżej

Jeśli chodzi o dodatkowe opcje dostępne poniżej tzn „rodzaj sprężenia” oraz „użyj dopasowywania rozmytego w celu wykonania scalenia” omówimy je sobie w jednym z kolejnych wpisów na tym blogu. Po wybraniu kolumn i zatwierdzeniu Ok utworzy się trzecie połączenie, które jest złączeniem dwóch utworzonych wcześniej zapytań.

Żeby dodać do już istniejących kolumn brakujące informacje z tabeli drugiej wystarczy w kolumnie z obiektami tabeli kliknąć w prawy górnym rogu przycisk, który umożliwia rozwinięcie kolumn z drugiej tabeli. Domyślnie zaznaczone są wszystkie kolumny tabeli oraz przycisk „Użyj oryginalnej nazwy kolumny jako prefiks” natomiast kolumna o nazwie ID_Klienta jest już widoczna w pierwszym w drugim zapytaniu, natomiast odhaczenie przycisku pozwoli zaoszczędzić nieco miejsca w nagłówku kolumny ukrywając prefiks składający się z nazwy tabeli (Sprzedawca zamiast Tabela1.Sprzedawca)

Po zatwierdzeniu przycisku klikając przycisk Ok i załadowaniu danych za pomocą przycisku „Zamknij i załaduj” naszym oczom ukaże się arkusz z danymi, który jest wynikiem połączenia dwóch tabel.

Czym jest Power Query?

Tematem dzisiejszego wpisu jest narzędzie o nazwie Power Query. Rozwiązanie to, chociaż należy do grupy umiejętności co najmniej średnio czy nawet zaawansowanego użytkownika nie oznacza, że mechanizm nie jest dostępny również dla osób początkujących. Wręcz przeciwnie, wszystko, co musisz mieć i wiedzieć, by skorzystać z narzędzia to przede wszystkim informację w jakich wersjach Excel możesz z niego korzystać.

Dla kogo jest Power Query?

Środowisko dodatku Power Query jest dostępne we wszystkich Excel 2016 lub nowszych Windows wersjach autonomicznych i planach subskrypcji usługi Microsoft 365 na karcie Dane w grupie & Przekształcanie. 

W wersji Excel niższej 2010 lub 2013 dostępny jest w ramach bezpłatnego dodatku który pobieramy z oficjalnej strony Microsoft i instalujemy na swoim komputerze. Możesz go pobrać tutaj

Oczywiście w kolejnych wersjach pakietu Office narzędzie Power Query może nieco różnić się w kolejnych wersjach. Lista różnic dostępna jest na oficjalnej stronie Microsoftu tutaj

Co zyskują użytkownicy Power Query?

Za pomocą tego narzędzia możemy importować dane z różnych źródeł, łączyć je ze sobą przekształcać i wyświetlać w postaci raportu dostosowanego do naszych potrzeb. Mówiąc językiem najbardziej prostym, zbieramy dane z przeróżnych źródeł, usuwamy niepotrzebne dane (kolumny, wiersze) łączymy potrzebne tabelki, i wyrzucamy do arkusza tylko to, co potrzebujemy.

Czyli tworząc takie zapytanie w Power Query możemy je podzielić na cztery obszary.

1. Połącz
   Pokaż narzędziu skąd pobrać dane (źródeł może być wiele)
2. Przekształcanie 
   Z podanych wcześniej danych źródłowych przekształć dane do przyjaznej postaci.
3. Łączenie
   Jeżeli istnieje zależność pomiędzy różnymi źródłami danych to połącz je według określonego klucza.
4. Załaduj
   Załaduj przygotowane wcześniej dane do arkusza.

Jako przykład użycia mechanizmu posłużę się jednym z wielu przykładów jego wykorzystania.

Pobieranie plików z całego katalogu

To jeden z najczęstszych przyczyn wykorzystania Power Query. Wyobraź sobie sytuację, że co jakiś czas generowany jest nowy plik. Taki plik musisz uwzględnić w swoim raporcie. Nieważne z jaką częstotliwością dochodzą nowe pliki czy jest to codziennie, co tydzień, co miesiąc. Chciałbyś wszystkie te dane złączyć (skleić) i przykładowo zsumować je ze sobą.

Obecnie nie ma łatwiejszego i szybszego sposobu jak wykorzystanie do tego Power Query. Żeby pobrać cały katalog zawierające pliki należy wejść we wspomnianą na początku wpisu kartę Dane. Następnie w sekcji Pobieranie i przekształcanie danych wybrać pierwszy dostępny przycisk Pobierz dane -> Z pliku -> Z folderu

Następnie program wyświetli okno dzięki któremu będzie mogli bez żadnych problemów wyklikać folder który chcielibyśmy sczytać. W moim przypadku będą to dane dotyczące znajomych z Facebooka. Pliki te pobieram systematycznie miesiąc po miesiącu.

Po kliknięciu przycisku Otwórz ujrzymy okienko, w którym mamy podgląd na wszystkie pliki w wybranym katalogu wraz z podstawowymi informacjami o nim. Między innymi znajdziemy tutaj dane dotyczące nazwy pliku, rozszerzenia daty utworzenia dokumentu, daty ostatniej modyfikacji oraz innych atrybutach, które na ten moment są ukryte. W ostatniej kolumnie możemy zauważyć ścieżkę do tego pliku. natomiast na samym dole pod listą cztery przyciski umożliwiające połączenie obecnego zapytania z innym, załadowania listy w takiej formie bezpośrednio do arkusza, oraz przycisk umożliwiający przekształceniu danych.

O połączeniach kilku zapytań to temat, któremu poświęcę w przyszłości cały wpis na blogu, który będzie kontynuacją serii o Power Query, dzisiaj skorzystamy z przycisku Załaduj. W tym miejscu chciałbym zwrócić uwagę na mały trójkąt z prawej strony przycisku który umożliwia skorzystanie z innych opcji ładowania danych natomiast na ten moment nie będę ich omawiał – zrobię to po kolejnych postach skorzystamy ze zwykłego ładowania do arkusza. Po naciśnięciu przycisku narzędzie załaduje do arkusza listę plików w wybranym katalogu.

Jeżeli jednak interesuje nas nie tyle lista plików we wskazanym folderze, ale sama zawartość arkuszy w wybranych plikach możemy zamiast ładowania bezpośredniego listy do arkusza skorzystać z przycisku Przekształć dane.

Po jego naciśnięciu ukaże nam się okno Edytora Power Query. Wraz z informacjami dobrze już Ci znanych z poprzednich zdjęć. natomiast w edytorze mamy możliwość przyjrzenia się wybranym plikom bardziej szczegółowo.

Oprócz przedstawionych wcześniej informacji dotyczących chociażby nazwy czy też ścieżki plików możemy sprawdzić inne atrybuty (zakładka Attributes). oraz możliwość podejrzenia zawartości każdego pliku (kolumna Content). Żeby dostać się do wnętrza atrybutu należy kliknąć przycisk umieszczony w prawym górnym rogu nagłówka Attributes. Rzwinie się lista pokazana poniżej.

Lista ta zawiera szereg różnych atrybutów, które możemy dodać do już istniejącego zestawienia. Możemy na przykład zobaczyć rozmiar każdego z zaimportowanych plików odhaczając wszystkie atrybuty klikają w przycisk Zaznacz wszystkie kolumny i klikając na Size, a następnie przycisk Ok

Naszym oczom ukaże się poprzednie zestawienie wraz z uwzględnieniem wybranego atrybutu Size (rozmiar)

Po naciśnięciu przycisku Zamknij i załaduj ponownie załadujemy zaktualizowane o jedną dodatkową kolumnę dane w arkuszu Excel. Zatrzymajmy się jednak przy edytorze Power Query i przyjrzyjmy się kolumnie Content. która również w prawym górnym rogu posiada przycisk dzięki któremu możemy przyjrzeć się strukturze plików które zaimportowaliśmy, w moim przypadku będzie to tylko jeden arkusz o nazwie friends który jest w każdym z zaimportowanych plików.

Jeżeli chcielibyśmy dostać się do wnętrza tych plików wybierzmy go sobie z poniższej listy. Na górze okna mamy mozliwość wyboru z listy rozwijanej domyślnego pliku przykładowego według którego będzie generowany podgląd oraz układ danych wybranego z poniższej listy nazwy arkusza. Wybrany plik mówi Excelowi jak powinien wyglądać idealny plik w katalogu. Po naciśnięciu nazwy arkusza po prawej stronie pojawi się podgląd arkusza. W moim przypadku w pierwszej kolumnie jest mój znajomy a po prawej data od kiedy każda osoba jest w kręgu moich znajomych. Po naciśnięciu przycisku Ok w arkuszu pojawi nam się zawartość wszystkich plików z wybranego katalogu z arkusz friends zgodnie z tym co zaznaczyliśmy.

Narzędzie Power Query umożliwia nam zaimportowanie danych bezpośrednio do arkusza Excel, natomias nie jest to jedyna opcja. Wejdźmy sobie na kartę Dane i wybierzmy pierwszą opcję Zapytania i połączenia. Po prawej stronie pojawi się lista zapytań. Między innymi Parametrym Przykładowy plik wybrany w poprzednim punkcie i funkcja potrzebna do zaczytania całego katalogu. Szczegółowo omówimy je sobie w kolejnych materiałach natomiast dzisiaj skupimy się na tym docelowym, ostatnim połączeniu które przed chwilą utworzyliśmy, klikając na nie prawą myszką i wybierając opcję Załaduj do… mamy możliwość zmiany domyślnej opcji ładowania danych w postaci tabeli do arkusza na przykład bezpośrednio do tabeli przestawnej. Gdy to zrobimy i potwierdzimy Excelowi o swoich zamiarach pojawi nam się okno pustej tabeli przestawnej wraz z opcjami.

Nie wiesz czym jest tabela przestawna? Zapraszam Ciebie do tego postu:

Tabele przestawne w programie Excel – czym są i do czego mogą nam się przydać – cz.1

Wybranie opcji tabeli przestawnej i odpowiedniemu rozmieszczeniu pól dobry pomysłem byłoby sprawdzenie ilości nowych znajomych w podziale na lata oraz miesiące. Czy umiesz dzięki temu stwierdzić który miesiąc obfitował w nowe Facebookowe znajomości?

Jak widzisz, przykład pokazany w dzisiejszym wpisie pozwala w sposób super szybkim i efektywnym pokazać zawartość z wielu plików odpowiednio je złączyć i pokazać w przynaznym raporcie. A czy Ty wykorzystujesz Power Query w swoich Excelowych projektach? Koniecznie daj znac w komentarzu!