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
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
