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
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.
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
Sprzężenie przy scalaniu zapytań oznacza rodzaj łączenia pomiędzy jedną tabelą a drugą. Istnieje 6 typów sprzężeń.
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
