Power Query cz2. Łączenie tabel
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.
