W dzisiejszym poście nauczymy się jak połączyć listy osób zapisanych na różne kursy za pomocą Power Query – dodatku do Excela. W przykładowych danych do tego zagadnienia przedstawionych na rysunku nr 1 mamy podane trzy listy adresów mailowych związanych z różnymi kursami. Naszym zadaniem jest połączenie tych list i stworzenie skumulowanej tabeli z adresami mailowymi z zaznaczonymi kursami, na jaki właściciel danego maila się zapisał.

rys. nr 1 — Przykładowe dane

Nasze zadanie ma również na celu to, żeby konkretna osoba która zapisała się na wszystkie trzy kursy w naszej tabeli występowała jeden raz (np. Andrzej@gazeta.pl zapisał się na trzy kursy).

Pierwszym krokiem jest zaczytanie naszych danych bazowych, w tym celu zaznaczamy dowolną komórkę w pierwszej tabeli i wybieramy polecenie Z tabeli (punkt nr 2 na rysunku nr 2) z karty Dane. Ułatwieniem tutaj jest, że tabele wejściowe zostały nazwane Tabela1, Tabela2 i Tabela3.

rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań z wczytaną tabelą Tabela1 (rys. nr 3). Z Zastosowanych kroków (Ustawienia zapytania) usuwamy krok Zmieniono typ, ponieważ jest on niepotrzebny.

rys. nr 3 — Edytor zapytań

Pozostałe tabele możemy wczytać w łatwy sposób, wystarczy że zduplikujemy sobie Zapytanie. Klikamy prawym przyciskiem myszy na nazwę Zapytanie1 i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 4). Czynność tę powtarzamy dwa razy ponieważ potrzebujemy trzech tabel.

rys. nr 4 — Duplikuj zapytanie

Zaznaczamy sobie zapytanie Tabela1 (2) i w pasku formuły zmieniamy nazwę Tabela1 na Tabela2 (oznaczone zieloną strzałką na rysunku nr 5). Wprowadzoną zmianę zatwierdzamy przyciskiem Enter.

rys. nr 5 — Zmiany w pasku formuły

Otrzymamy zapytanie Tabela1 (2) z wczytaną drugą tabelą z danymi (osoby zapisane na kurs Dashboardy) przedstawione na rysunku nr 6. Po raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.

rys. nr 6 — Połączone zapytania

Analogicznie wygląda sytuacja dla trzeciego zapytania. Klikamy na nazwę zapytania Tabela1 (3) i w pasku formuły zmieniamy nazwę Tabela1 na Tabela3. Wprowadzoną zmianę zatwierdzamy przyciskiem Enter. Otrzymamy trzecie zapytanie z wczytanymi danymi z tabeli numer 3 przedstawionymi na rysunku nr 7. Po raz kolejny usuwamy niepotrzebny krok Zmieniono typ z Zastosowanych kroków.

rys. nr 7 — Dane z trzech tabel

Kolejnym krokiem jest zmiana nazwy zapytań nr 2 i 3 we Właściwościach w panelu bocznym z Ustawieniami zapytania (oznaczone zieloną strzałką na rysunku nr 8).

rys. nr 8 — Zmiana nazwy zapytania

Kiedy już mamy zaczytane wszystkie trzy tabele, możemy je połączyć. Rozwijamy polecenie Połącz (punkt nr 2 na rysunku nr 9) z karty Narzędzia główne i wybieramy polecenie Dołącz zapytania (punkt nr 3 na rysunku nr 9).

rys. nr 9 — Dołącz zapytania

Pojawi nam się okno Dołączanie, gdzie zaznaczamy checkbox przy opcji Co najmniej trzy tabele (punkt nr 1 na rysunku nr 10). Następnie dodajemy Tabelę2 i Tabelę3 z pola Dostępne tabele do pola Tabele do dołączenia za pomocą przycisku Dodaj (punkt nr 2 na rysunku nr 10). Dołączone tabele zatwierdzamy przyciskiem OK.

rys. nr 10 — Okno Dołączania zapytań

Otrzymamy jedną tabelę ze skumulowanymi danymi ze wszystkich trzech tabel (rys. nr 11).

rys. nr 11 — Skumulowane dane

Mimo że dane zostały dołączone to nadal osoby które zapisały się na większą ilość kursów są wyświetlane osobno, a nam chodzi o to by takie osoby wyświetlane były w tabeli tylko raz. W tym celu musimy anulować przestawienie innych kolumn poza kolumną Mail. Klikamy prawym przyciskiem myszy na tytuł kolumny Mail i z podręcznego menu wybieramy polecenie Anuluj przestawienie innych kolumn (rys. nr 12).

rys. nr 12 — Anuluj przestawienie innych kolumn

Otrzymamy dane przedstawione na rysunku nr 13, gdzie stworzą nam się nowe kolumny. Kolumna Atrybuty będzie zawierała nazwy kolumn, natomiast kolumna Wartość będzie zawierała wartości jakie były przypisane do odpowiednich adresów mailowych w tych kolumnach.

rys. nr 13 — Dane z nowymi kolumnami

Najważniejszy efekt działania tego polecenia to pozbycie się z danych wartości null. W kolejnym etapie musimy przekształcić jedną z tych kolumn (Atrybuty bądź Wartość), zrobimy operację odwrotną. Z karty Przekształć wybieramy polecenie Kolumna przestawna (punkt nr 2 na rysunku nr 14).

rys. nr 14 — Kolumna przestawna

Otworzy nam się okno Tabeli przestawnej, gdzie stworzymy nowe kolumny przy użyciu kolumny Atrybyt, czyli powstaną kolumny z nazwami VBA, Dashboardy i Excel (rys. nr 15). Odpowiednio w każdej z tych kolumn będzie wpisana wartość z kolumny Wartość (punkt nr 1 na rysunku nr 15). Ponadto musimy wybrać w Opcjach zaawansowanych wybieramy w jaki sposób będą agregowane nasze dane – Nie agreguj (punkt nr 2 na rysunku nr 15). Wybrane parametry zatwierdzamy przyciskiem OK.

rys. nr 15 — Okno kolumny przestawnej

Otrzymamy dane przedstawione na rysunku nr 16, gdzie mamy już skumulowane dane. Przykładowo właściciel maila Andrzej@gazeta.pl występuje tylko raz na liście mimo że zapisał się aż na trzy kursy.

rys. nr 16 — Skumulowane dane

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne.

rys. nr 17 Zamknij i załaduj

Ponieważ jednocześnie pobieraliśmy dane z trzech tabel, to utworzyło nam się w Excelu tylko połączenie. Dane te będą widoczne w panelu bocznym Zapytania. Gdyby nie były widoczne należy wybrać polecenie Pokaż zapytania z karty Dane (rys. nr 18).

rys. nr 19 — Pokaż zapytania

Interesuje nas zapytanie Tabela1, bo to w tym zapytaniu połączyliśmy wszystkie dane (rys. nr 19).

rys. nr 19 — Zapytanie Tabela1

Klikamy prawym przyciskiem myszy na nazwę zapytania Tabela1 i z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 20).

rys. nr 20 — Załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób przedstawienia załadowanych danych jako Tabela, a następnie lokalizację tych danych jako Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 21). Powyższe parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 21 — Okno Ładowanie do

Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 22.

rys. nr 22 — Dane w Excelu

Przy tworzeniu danych został zrobiony błąd, którego nie zauważyliśmy. W Excelu w oknie Zapytania dotyczące skoroszytu jest on wyszczególniony (rys. nr 23).

rys. nr 23 — Błąd w zapytaniu

Po kliknięciu na ten błąd, otworzy nam się edytor zapytań z wczytanym błędem w zapytaniu Tabela1 (rys. nr 24).

rys. nr 24 — Edytor zapytań z błędem

W panelu bocznym Ustawienia zapytania klikamy na Źródło w Zastosowanych krokach. Mamy podany numer wiersza w którym był błąd, więc możemy go łatwo znaleźć. Kiedy klikniemy na ten wiersz wyświetli nam się pod tabelą informacja o tym błędzie (rys. nr 25).

rys. nr 25 — Informacja o błędzie

Błąd ten dotyczy maila Sebastian@yahoo.com, który wystąpił w danych źródłowych dwa razy (dla tego samego kursu) co widać na rysunku nr 26.

rys. nr 26 — Miejsce błędu

Musimy poprawić te dane W Excelu i usunąć powtarzający się adres mailowy, następnie klikamy prawym przyciskiem myszy na dowolną komórkę w danych z zapytania i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 27).

rys. nr 27 — Odśwież

Otrzymamy prawidłowe dane przedstawione na rysunku nr 28.

rys. nr 28 — Prawidłowe dane

Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.