fbpx

Scalanie zapytań w Power Query

Wstęp

Power Query to potężny dodatek do programu Excel. Microsoft widząc na rynku rosnące zapotrzebowanie użytkowników na przekształcanie danych stworzył narzędzie Power Query, które pozwala modyfikować dane w sposób dużo bardziej zautomatyzowany i wygodniejszy niż przy pomocy dotychczasowych narzędzi dostępnych w samym Excelu. Jedną z bardziej podstawowych czynności, z której korzysta się bardzo często to tak zwane scalanie zapytań (Merge Queries). Poniżej opisano w jaki sposób można dokonywać takich scaleń. Więcej o Power Query dowiesz się na jednym z naszych kursów – Kurs Microsoft Power Query

Dane i pobranie do Power Query

Pierwszą rzeczą jest posiadanie danych, które można scalić. Poniżej zaprezentowano dane składające się z dwóch arkuszy. Zdarzenia i Użytkownicy.

Należy pamiętać, że nie wszystkie dane zawsze można scalić. Gdybyśmy przykładowo mieli w jednym arkuszu sprzedaż biletów do kin na Podkarpaciu a w drugim sprzedaż napojów w Zachodniopomorskim to prawie na pewno nie dałoby się takich danych scalić. Wynika to z tego, że nie moglibyśmy znaleźć tak zwanej kolumny klucz czyli wspólnego elementu dla obu tabel. W powyższym przykładzie naszą kolumną klucz jest nr Użytkownika. Jest to ta sama kolumna, która pozwoli nam połączyć dane. Przed importem danych warto z naszych obu zakresów zrobić tabele. W tym celu, klikamy na jedną komórkę z danymi w arkuszu Zdarzenia przechodzimy na kartę Wstawianie i wybieramy po lewej stronie panelu Tabela. Następnie przechodzimy na kartę Projekt Tabeli po lewej stronie zmieniamy nazwę na Zdarzenia (ułatwia zadanie w momencie gdy pracujemy na kilku tabelach). Dokonujemy dokładnie tej samej operacji na arkuszu Użytkownicy i zapisujemy plik Excela.

Teraz importujemy dane do Power Query.  Na karcie Dane wybieramy Pobierz Dane > Z Pliku > Ze skoroszytu. Po wybraniu odpowiedniego pliku należy pamiętać o opcji Wybierz Wiele elementów. Wybieramy tabelę Zdarzenia i Użytkownicy. Ostatnim krokiem w dolnej części ekranu jest wybranie opcji Przekształć dane.

Otwiera nam się narzędzie Power Query z naszymi danymi, które są widoczne po lewej stronie w panelu Zapytania.

Scalanie danych w Power Query

Teraz najistotniejsza część. Aby scalić oba arkusze, na karcie Narzędzia główne w sekcji Połącz wybieramy Scal zapytania. Jeśli klikniemy w strzałkę po lewej stronie Scal zapytania jest jeszcze możliwość wybrania Scal zapytania jako nowe. Wówczas wynik scalenia będzie nowym zapytaniem. W tym wypadku wykonujemy scalenie będąc na zapytaniu Użytkownicy.

W nowym oknie w pierwszej tabeli wybieramy wspólną kolumnę – klikamy na kolumnę Nr użytkownika. Niżej należy najpierw wybrać drugą tabelę. Wybieramy z listy Zdarzenia.

Tutaj również zaznaczamy kolumnę Nr użytkownika.

Rodzaj sprzężenia wybieramy na Lewe zewnętrzne. W wyniku takiego sprzężenia otrzymamy wszystkie wiersze z tabeli użytkownicy i pasujące wiersze z tabeli Zdarzenia. W momencie gdy jeden użytkownik był przypisany do wielu zdarzeń to w wyniku pojawią się wszystkie te wiersze z tabeli Zdarzenia, a dane z tabeli użytkownika zostaną powielone.

Następnie zatwierdzamy Ok.

W wyniku scalenia otrzymaliśmy nową kolumnę gdzie wszystkie dane zapisane są jako Table. Żeby zobaczyć dane należy je rozwinąć wybierając na nagłówku Zdarzenia przycisk po prawej stronie. Z listy wybieramy kolumny, które nas interesują. W tym wypadku wybrano trzy: Nazwa raportu, Data, Czas.

W efekcie otrzymujemy dane jak poniżej:

Nazwy nagłówków można zmieniać dowolnie jak w zwykłej komórce Excela.

Aby przekształcone dane przerzucić do Excela należy przejść na kartę Narzędzia Główne i wybrać po lewej stronie Zamknij i załaduj.

Jeżeli podobał Ci się ten artykuł i chciałbyś dowiedzieć się więcej o dodatkach Microsoft Excel to zapraszam do zapoznania się z katalogiem naszych kursów – Kursy Power BI

Rodzaje sprzężeń

Sprzężenie przy scalaniu zapytań oznacza rodzaj łączenia pomiędzy jedną tabelą a drugą. Istnieje 6 typów sprzężeń.

  • Lewe zewnętrzne – wszystkie z pierwszej pasujące z drugiej (użyte w poprzednim przykładzie)
  • Prawe zewnętrzne  – wszystkie z drugiej, pasujące do pierwszej
  • Pełne zewnętrzne – tylko pasujące wiersze
  • Wewnętrzne – tylko pasujące wiersze
  • Lewe anty – wiersze tylko w pierwszej
  • Prawe anty – wiersze tylko w drugiej

W poniższym przykładzie wykorzystamy sprzężenie Prawe zewnętrzne.

Podobnie jak w poprzednim przykładzie przenosimy te same dane do Power Query i uruchamiamy narzędzie Scal zapytania. Jako pierwszą tabelę wybieramy Zdarzenia, a jako drugą Użytkownicy. Należy także wskazać odpowiadające sobie kolumny (w tym przypadku kolumnę Nr użytkownika). Rodzaj sprzężenia – Prawe zewnętrzne.

Warto tutaj dodać że Lewe oznacza pierwszą tabele – u góry, natomiast prawy oznacza drugą – u dołu.

Teraz aby zobaczyć dane z arkusza Zdarzenia wybieramy w nagłówku użytkownicy, z prawej strony ikonkę ze strzałką w lewo i prawo, wybieramy kolumny z tej tabeli, które chcemy zobaczyć w wyniku (w naszym przykładzie wybieramy wszystkie kolumny) i zatwierdzamy ok.

Wybraliśmy sprzężenie Prawe zewnętrzne co oznacza, że widzimy wszystkie dane z tabeli użytkownicy. W momencie gdy z lewej tabeli (zdarzenia) nie ma przypisanego użytkownika to tabela zwraca null czyli tak zwane wartości nieokreślone.

W poniższym przykładzie wykorzystamy sprzężenie wewnętrzne – tylko pasujące wiersze.

Scalanie wewnętrzne oznacza, że w odpowiedzi otrzymamy tylko te wiersze, które pasują do siebie z obu tabel. Innymi słowy wynikiem są te rekordy, gdzie użytkownicy mają przypisane zdarzenia – tylko i wyłącznie.

Jeżeli interesuje Cię Power Query a także pozostałe rodzaje sprzężeń to zachęcam do zapoznania się z naszym programem – Kurs Microsoft Excel Power Query

Zobacz również:

Szkolenia:

udostępnij:
Gratulacje!
Wniosek został wysłany prawidłowo.
Dziękujemy za przesłanie zapytania. Skontaktujemy się z Tobą najszybciej jak to możliwe.