Pobierz przykładowy plik z danymi do ćwiczeń
Dzisiaj pokażę, jak możemy porównać dwie tabele w Power Query. Załóżmy, że mamy listę osób, które uczęszczają na kółko informatyczne w tym roku, oraz listę osób uczęszczających na kółko informatyczne w roku ubiegłym. Będziemy chcieli porównać te dwie listy, aby sprawdzić następujące dane:
- Jacy uczniowie są na obu listach (tj. uczestniczą w kółku informatycznym w obecnym roku, oraz w roku ubiegłym)
- Jacy uczniowie uczestniczą w kółku informatycznym jedynie w roku obecnym
- Jacy uczniowie „wypisali się” z kółka (tj. uczestniczyli rok temu, a obecnie już nie uczestniczą)
- Oprócz tego chcemy sprawdzić poprawność danych np. załóżmy, że dane są ręcznie przepisywane z karteluszków i nie mamy pewności czy w obu tabelach uczeń ma wpisaną taką samą datę urodzenia.
Tym razem dane są zapisane w jednym arkuszu (nie trzeba ich importować z pliku CSV). Dla ułatwienia obie listy oznaczyłem jako tabele (tj. zaznaczyłem je i wybrałem z menu Wstawianie opcję Tabela).
Porównywanie dwóch tabel
- W pierwszym kroku należy zaimportować obie tabele do programu Power Query. W tym celu kliknij gdziekolwiek w obrębie pierwszej tabeli a następnie z karty Dane wybierz polecenie Z arkusza (w starszych wersjach Power Query ta opcja może się nazywać Z tabeli/zakresu).
- Załaduje się edytor Power Query. Na karcie Narzędzia główne kliknij wdolnączęść przycisku Zamknij i załaduj i wybierz opcje Zamknij i załaduj do…
- W oknie dialogowym Importowanie danych zaznacz opcję Utwórz tylko połączenie (nie chcemy ładować zaimportowanych danych np. do nowego arkusza, ale chcemy tylko było zapisane połączenie).
- Kroki od 1 do 3 wykonaj dla drugiej tabeli
- Mając zaimportowane dwie tabele do Power Query możemy przystąpić do działania. W tym celu z karty Dane wybierz polecenie Pobierz dane -> Połącz zapytania -> Scal
- Pojawi się okno dialogowe Scalanie. Jako pierwszą tabelę wybierz Tabela1 a jako drugą Tabela2. Zwróć uwagę że liczba porządkowa w pierwszej kolumnie nie jest kluczem wyszukiwania między tabelami (tj. ta sama osoba w dwóch różnych tabelach może mieć różną liczbę porządkową). Dlatego naszym kluczem jest para kolumn imie i nazwisko. W tym celu najpierw w górnej tabeli kliknij na nagłówku imię a potem trzymając wciśnięty klawisz CTRL na nagłówku kliknij w nazwisko. W drugiej tabeli zrób to samo (pamiętaj, że jeśli najpierw klikałeś w imię w pierwszej tabeli to w drugiej musisz zaznaczać kolumny w tej samej kolejności). Zwróć uwagę, że na nagłówkach tabel przy imię pojawi się jedynka a przy nazwisku dwójka (bo w takiej kolejności w obu tabelach zaznaczaliśmy kolumny).
- Jako rodzaj dopasowania wybierz Pełne zewnętrzne i kliknij w przycisk OK
- Załadował nam się edytor Power Query. W przypadku kolumny Tabela2 kliknij w przycisk z strzałeczkami znajdujący się w nagłówku tabeli i każ załadować wszystkie kolumny
- Przeanalizuj tabelę. Jeśli są nulle „z lewej strony”to znaczy, że dana osoba nie jest wpisana w Tabeli1 (osoby uczęszczające na kółko rok temu). Jeśli nulle są z prawej strony to sytuacja jest odwrotna. Jeśli w danym wierszu nie ma wartości null to znaczy, że dana osoba znajduje się w obu tabelach to znaczy że jest członkiem kółka informatycznego obecnie jak i rok temu.
Wskazówka: Owe ćwiczenie mogliśmy wykonać nieco inaczej tj. mogliśmy wybrać rodzaj sprzężenia anty lewe albo anty prawe – wtedy otrzymalibyśmy tylko te rekordy, które nie znajdują się w danej tabeli. Więcej informacji na ten temat znajdziesz w artykule rodzaje sprzężeń.
Porównywanie dat urodzenia
Obie listy uczestników były tworzone ręcznie i nie mamy pewności, czy przepisując daty urodzenia nie doszło do jakiejś pomyłki. W tym ćwiczeniu chcemy wyłapać osoby, które mają wpisane różne daty urodzenia. W tym celu wykonaj następujące czynności:
- Zaimportuj obie tabele do edytora Power Query jako połączenia (jeśli zrobiłeś to w poprzednim ćwiczeniu, pomiń ten krok)
- Znów dokonamy połączenia dwóch tabel, ale tym razem jako rodzaj sprzężenia wybierzemy Wewnętrzne (tym razem nas interesują tylko te osoby, które są członkami kółka w roku obecnym i poprzednim).
- W edytorze Power Query rozwiń drugą tabelę
- Kliknij na kartę Dodaj kolumnę i wybierz polecenie Kolumna warunkowa. Następnie w oknie dialogowym Dodawanie kolumny warunkowej wypełnij dane jak na poniższym rysunku:
- Pojawiła się dodatkowa kolumna o nazwie CzyTakieSameDaty. W zależności od sytuacji w danej kolumnie pojawia się komunikat OK lub błąd.
- Jeśli danych jest dużo, to przed zaimportowaniem ich do nowego arkusza można wybrać filtr, że interesują nas tylko te rekordy, które w kolumnie CzyTakieSameDaty mają wybraną wartość błąd.
Zaimportuj dane do nowego arkusza i obdzwoń osoby, które w obu tabelach mają wpisane różne daty urodzenia.
