Power Query w Excelu – Twój praktyczny poradnik krok po kroku
Power Query to potężne narzędzie w Excelu do importowania, przekształcania i łączenia danych z różnych źródeł. Dzięki niemu możesz automatyzować żmudne zadania związane z przygotowaniem danych do analiz. W tym przewodniku pokażę Ci krok po kroku, jak wykorzystać najważniejsze funkcje Power Query w praktyce.
A w dalszej części poradnika:
- Jak zaimportować dane do Power Query
- Krótki opis edytora Power Query
- Usuwanie zbędnych wierszy
- Zmiana nazw nagłówków w edytorze Power Query
- Usuwanie zbędnych kolumn
- Dzielenie na kolumny i usuwanie zbędnego tekstu w Power Query
- Zmiana typu danych w Power Query
- Załadowanie danych do Excela i odświeżenie danych
- Duplikowanie i odwołanie do zapytań
- Grupowanie danych w Power Query
- Filtrowanie danych
- Naprawa błędów i cofanie kroków postępowania
- Kolumna warunkowa
- Import danych do Power Query z pliku CSV
- Scalenie danych
- Kolumny przestawne
- Podsumowanie
Jak zaimportować dane do Power Query
W tym poradniku posłużymy się przykładem zwykłej tabeli w Excelu zawierającej około 100 rekordów zawierających dane dotyczące zamówień.
Aby zaimportować te dane do Power Query zaznacz dowolną komórkę wewnątrz zakresu i naciśnij polecenie Z tabeli/zakresu znajdującej się na karcie Dane w grupie Pobieranie i przekształcanie danych.
Excel automatycznie przekształci ten zakres na Tabelę Excelową. Zwróć uwagę na to aby tabela obejmowała cały zakres danych. W naszym przypadku mamy kilka pustych rekordów i musimy ręcznie zaznaczyć zakres $A$1:$F$106. Po kliknięciu ok otworzy się nam edytor Power Query z wczytanymi danymi.
Jeśli nie możesz znaleźć tego polecenia importu lub nie otwiera Ci się edytor Power Query zobacz artykuł Co to jest Power Query.
Krótki opis edytora Power Query
Edytor Power Query otwiera się w osobnym oknie z przejrzystym układem. Po lewej stronie znajduje się lista zapytań, na środku widzisz podgląd danych, a po prawej – panel zastosowanych kroków, który pokazuje wszystkie operacje wykonane na danych. Na górze jest wstążka z narzędziami do filtrowania, przekształcania, łączenia i analizowania danych.
Usuwanie zbędnych wierszy
Pracując w edytorze Power Query działamy na całych wierszach i kolumnach. Wszystkie polecenia które będziemy wybierać z górnej wstążki odnoszą się do zaznaczonych kolumn lub wierszy spełniających określone warunki.
Pierwszym krokiem w porządkowaniu danych będzie usunięcie pustych wierszy – Narzędzia główne > Usuń wiersze > Usuń puste wiersze (A)
Te polecenie będzie usuwało wszystkie rekordy tabeli które we wszystkich kolumnach mają puste wartości.
Następnie musimy usunąć ostatni wiersz z podsumowaniem. Poprzednie polecenie nie usunęło tego wiersza ponieważ 2 z 5 kolumn zawiera wartości.
Aby usunąć ostatni wiersz tabeli wybieramy polecenie Narzędzia główne > Usuń wiersze > Usuwanie końcowych wierszy (B)
W nowym oknie wpisz 1 aby usunąć jeden końcowy wiersz, po zatwierdzeniu gotowe
Zmiana nazw nagłówków w edytorze Power Query
Teraz przyjrzyjmy się nagłówkom tabeli z danymi. Z racji tego, że w danych mieliśmy podwójny nagłówek. Nagłówek górny został rozbity a jego nazwa znajduje się w pierwszej komórce. Natomiast dolny nagłówek jest traktowany po prostu jako dane.
My będziemy chcieli przenieść nazwy z pierwszego wiersza do nagłówków
Na szczęście Power Query ma dla nas szybkie polecenie do rozwiązania tego problemu.
Zastosuj polecenie Użyj pierwszego wiersza jako nagłówków (karta Narzędzia główne). Dzięki temu nasze nagłówki są już prawidłowo opisane.
Usuwanie zbędnych kolumn
Ostatnia kolumna zawierająca uwagi jest nam nie potrzebna, nie daje nam żadnych informacji więc ją usuniemy.
Aby usunąć dowolną kolumnę lub kilka kolumn naraz należy zaznaczyć nagłówek kolumny i wybrać polecenie Usuń kolumny (Karta Narzędzia główne). Kolumna piąta została usunięta.
Dzielenie na kolumny i usuwanie zbędnego tekstu w Power Query
Na potrzeby zestawienia potrzebujemy podzielić Imię i nazwisko zamawiającego na dwie kolumny.
Ale oprócz tego kilka osób przed nazwą ma wpisane Pan lub Pani.
Zacznijmy od tego, że usuniemy wszystkie formy grzecznościowe. Zaznacz kolumnę Imię i Nazwisko, wybierz polecenie Zamienianie wartości (karta Narzędzia główne). W nowym oknie wpisz „Pan ” zamień na „” (pustą wartość). Powtórz tą czynność dla „Pani ”.
Teraz w kolumnie mamy tylko Imiona i Nazwiska. Zaznacz całą kolumnę i wybierz polecenie Podziel kolumny według ogranicznika. W nowym oknie podaj ogranicznik „spacja”. Po zatwierdzeniu Power Query podzieli kolumnę na dwie. Teraz pozostało nam ręcznie zmienić nazwy nagłówków poprzez podwójne kliknięcie w nazwę nagłówka wpisz dla pierwszej kolumny „Imię” a dla drugiej „Nazwisko”
Zmiana typu danych w Power Query
Po lewej stronie od nazwy każdego nagłówka kolumny znajduje się znacznik symbolizujący typ danych. Jest to niezwykle istotne aby nadać prawidłowy typ danych. Wiele poleceń jest przeznaczonych tylko dla wybranych rodzajów danych np. data / tekst / waluta. W przypadku gdy nie nadamy prawidłowego typu danych niektóre polecenia nie zadziałają a dane załadowane do Excela nie będą się prawidłowo wyświetlać.
Aby nadać typ danych możemy wybrać polecenie Wykryj typ danych (karta Przekształć) wtedy wszystkie kolumny otrzymają typ przypisany przez Power Query (nie zawsze prawidłowo). Typ danych możemy nadać ręcznie zaznaczając kolumnę i wybierając typ danych z polecenia (karta Przekształć) np. Waluta dla kolumny Sprzedaż.
Załadowanie danych do Excela i odświeżenie danych
Na tą chwilę mamy odpowiednio przekształcone dane i możemy je załadować do Excela. Ale zanim to zmieniamy jeszcze nazwę tego zapytania z „Tabela1” na „Zamówienia”. Wpisujemy tą nazwę w polu Nazwa po prawej stronie.
Aby załadować dane do Excela wystarczy że klikniemy polecenie Zamknij i załaduj (wtedy dane zostaną załadowane do nowego arkusza) lub Zamknij i załaduj do (wtedy możemy wybrać gdzie i w jakiej formie załadować dane do Excela)
Dane z Power Query do Excela możesz załadować w formie:
- Tabeli
- Raportu w formie tabeli przestawnej
- Wykresu przestawnego
- Utworzyć tylko połączenia
Możesz też dodać dane do modelu danych – tutaj zastosowanie ma Power Pivot
W Excelu po prawej stronie zobaczysz nowe okno Zapytania i połączenia, w którym znajdziesz wszystkie dostępne zapytania dla tego skoroszytu.
Po kliknięciu prawym przyciskiem myszy zobaczysz dostępne opcje dla tego zapytania tj. edycja, kopiowanie, zmiana nazwy itp.
Istotną kwestią jest aktualizacja danych. Gdy dane źródłowe ulegną zmianie nie musisz wykonywać całej procedury przekształceń. Wystarczy jedynie, że odświeżysz zapytanie a edytor Power Query wykona wszystkie kroki przekształceń i zwróci nową tabelę z danymi.
Duplikowanie i odwołanie do zapytań
W Power Query mamy możliwość utworzenia duplikatu zapytania – duplikaty tworzymy gdy chcemy tworzyć inne zapytanie, które będą przekształcać dane do różnych form wynikowych.
Odwołanie jest również duplikatem zapytania ale odwołuje się ono do zapytania pierwotnego. Więc jeśli wprowadzimy zmianę w pierwszym zapytaniu, odwołanie również uwzględni tą zmianę w przeciwieństwie do duplikatu.
Duplikat zapytania możemy stworzyć z poziomu Excela lub Power Query – wystarczy, że klikniemy ppm na zapytaniu i wybierzemy Duplikuj lub Odwołanie.
W naszym przypadku utworzę Odwołanie o nazwie „Grupowanie”
Grupowanie danych w Power Query
Teraz pokażę jak można szybko w Power Query grupować dane. W zapytaniu Grupowanie będę chciał utworzyć tabelę przedstawiającą sumę wartości zamówienia i średnią wartość zamówienia dla poszczególnych miesięcy w roku.
Najpierw posortuje dane w kolejności rosnącej (klikam na znacznik trójkąta przy nazwie nagłówka i wybieram Sortuj rosnąco) później przekształcę daty aby wskazywały ostatni dzień miesiąca. Zaznaczam kolumnę „Data zamówienia” i stosuję polecenie Data/Miesiąc/Koniec miesiąca (karta Przekształć).
Teraz wybieram polecenie Grupuj według (karta Narzędzia Główne)
W nowym oknie wybieram Zaawansowane. W górnym polu wybieram z listy kolumnę „Data zamówienia” a pozostałe dwie kolumny będą sumowały i liczyły średnią dla kolumny Sprzedaż. Dzięki temu otrzymaliśmy poniższą tabelę zgrupowanych wartości.
Filtrowanie danych
Power Query ma dużo możliwości filtrowania. Tabelę utworzoną w poprzednim dziale będę chciał przefiltrować i pozostawić tylko daty z 2024 roku. W tym celu
Aby otworzyć okno filtrowania należy kliknąć w znacznik „trójkącika” przy nazwie nagłówka kolumny. Każdy typ danych charakteryzuje się innym sposobem filtrowania.
W przypadku pola z datami mamy dużo opcji filtrowania w tym przypadku podaję zakres dat pomiędzy 01.01.2024 a 31.12.2024.
Naprawa błędów i cofanie kroków postępowania
W Power Query nie mamy możliwości cofania poleceń jak w Excelu za pomocą skrótu Ctrl+Z. W Power Query wszystko co robimy jest zapisywane podobnie jak makra w Excelu. Wszystkie kroki postępowania pokazane są po prawej stronie w oknie Zastosowane kroki.
W każdym momencie możemy przejść do dowolnego poprzedniego kroku i go zmienić. Ale tutaj musisz uważać bo każdy następny krok odwołuje się do poprzedniego kroku i jeśli zmienisz coś w poprzednich krokach w kolejnych możesz otrzymać błąd (jeśli nieodpowiednio nadpiszesz ten krok). Tutaj zalecam ostrożność.
Aby usunąć krok wystarczy kliknąć czerwony x. Aby zmienić krok możemy edytować go gdy widzimy po prawej stronie ikonę koła zębatego (gdy go nie ma możemy edytować polecenia w pasku poleceń.
Polecenia w Power Query pisane są w języku M. Jak edytować takie polecenia i tworzyć własne oraz polecenia niestandardowe uczę w kursie Power Query
Kolumna warunkowa
Wróćmy do zapytania Zamówienia, do którego będę chciał dodać nową kolumnę, w której wpiszę, że jeśli wartość zamówienia jest większa niż 10.000 zł będzie to zamówienie „VIP inaczej „Zwykłe”.
Po kliknięciu polecenia Kolumna warunkowa (karta Dodaj kolumnę) pojawi się okno dodawania warunku, działa to podobnie jak funkcja Jeżeli w Excelu.
W naszym przypadku sprawdzamy wartość w kolumnie Sprzedaż. Jeżeli jest większa niż 10.000 zł to wprowadzamy wartość wyjściową „VIP”, w przeciwnym razie „Zwykłe”. Efekt widoczny jest poniżej.
Import danych do Power Query z pliku CSV
Do Power Query możemy pobrać dane z różnych źródeł, plików i folderów. Na potrzeby tego wpisu zaimportujemy dane z pliku CSV. W tym celu klikam prawym przyciskiem myszki na lewym polu zapytania i wybieram Nowe zapytania/Plik/Plik tekstowy lub CSV
Pojawi nam się okno w którym możemy wybrać pochodzenie pliku, ogranicznik wartości oraz sposób wykrywania danych a następnie zatwierdzamy wybór. Po czym przed naszymi oczami powinno pojawić się nowe zapytanie „Firmy” z danymi firm i numerem ID.
Scalenie danych
Power Query pozwala nam na scalanie danych. W zapytaniu Zamówienia mamy kolumnę „Id firmy”, którą będę chciał zamienić na Nazwę i Miejscowość firmy z zapytania Firmy.
Zanim powiążemy zapytanie Zamówienia z zapytaniem Firmy, muszę upewnić się że wartości w kolumnach są jednakowe. Obecnie tak też nie jest, dlatego w zapytaniu Firmy przed numerem dodam słowo „ID”.
W tym celu korzystam z polecenia Dodaj prefiks (karta przekształć). W oknie które się pojawi wpisuję „ID”, dzięki czemu otrzymuję poniższy efekt.
Teraz wartości w obu tabelach są takie same i mogę scalić wartości. Przechodzę do zapytania Zamówienia i wybieram polecenie Scal zapytania (karta Narzędzia główne).
W oknie zaznaczam dla zapytania Zamówienia kolumnę „Id firmy”, następnie wybieram powiązane zapytanie – Firmy, w którym zaznaczam kolumnę wiążącą „Id”. Rodzaj sprzężenia – lewe zewnętrzne.
Efektem czego utworzona została nowa kolumna z wartościami przedstawionymi w tabelach wewnętrznych.
Następnie muszę wybrać które kolumny chcę zaimportować do tej tabeli, robię to rozwijając tabele przyciskiem pokazanym strzałką.
I wybieram, że chcę rozwinąć kolumnę „Nazwa firmy” i „Miasto”
Kolumny przestawne
Niezwykle przydatnym poleceniem w Power Query są kolumny przestawne i anulowanie przestawienia kolumn.
Pokażę tutaj prosty przykład zastosowania kolumny przestawnej. Będę chciał przestawić łączną wartość sprzedaży dla każdej firmy dla każdego miesiąca.
Utworzę nowe zapytanie o nazwie Zestawienie na podstawie zapytania Zamówienia. Następnie pozostawię tylko potrzebne mi kolumny „Nazwa Firmy”, „Sprzedaż” i „Data” – pozostałe kolumny usunę za pomocą polecenia Usuń kolumny\Usuń inne kolumny (karta Narzędzia główne)
Teraz zamienię Daty na nazwy miesiąca. Zaznaczam kolumnę „Data zamówienia” i wybieram polecenie Data/Miesiąc/Nazwa miesiąca (karta Przekształć).
Teraz zaznaczam kolumnę „Nazwa firmy” i wybieram polecenie Kolumna przestawna (karta Przekształć). W nowym oknie podaję kolumnę wartości czyli „Sprzedaż” (to jest ta kolumna, która jest sumowana). Efektem czego jest poniższa tabela, która w wierszach zawiera miesiące a w kolumnach łączną wartość sprzedaży dla każdej firmy.
Aby ułożyć miesiące w odpowiedniej kolejności należy przejść do kroku, w którym zmieniliśmy nazwy miesięcy i wcześniej posortować dane na podstawie daty od najwcześniejszej do najpóźniejszej.
Podsumowanie
Ten poradnik pokazuje tylko kilka podstawowych przykładów zastosowania Power Query. Możemy w nim wykonywać masę innych przekształceń, transpozycji dołączania danych, obliczeń, importowania tylko wybranych danych z wielu plików, ze stron internetowych, pdf-ów i obrazów, tworzenia własnych funkcji, stosowania funkcji niestandardowych, parametryzacji kodu i wielu innych.
Dla osób, które chciały by bardziej zagłębić się w ten temat przygotowałem kompleksowy kurs Power Query, w którym przedstawiam te możliwości. Do tego dołączam pliki na których można przećwiczyć swoje umiejętności, zadania do samodzielnego wykonania i wiele innych.
Co to jest Power Query i jak może usprawnić Twoją pracę z danymi?
Power Query to narzędzie do pracy z danymi za pomocą którego możemy w łatwy sposób importować, przekształcać a następnie analizować dane z różnych źródeł, takich jak bazy danych, pliki tekstowe, foldery czy nawet strony internetowe. Power Query jest darmowym dodatkiem do Excela a także jest dostępny w innych produktach firmy Microsoft tj. Power BI.
Power Query dynamiczne źródło danych – zmiana źródła z listy
Automatyczna zmiana pliku z którego pobieramy dane do Excela.
Konwersja PDF do Excela. Jak Pobrać Tabelę PDF do Excela 365
W tym poradniku dowiesz się w jaki sposób pobrać do Excela 365 dużą tabelę znajdującą się na wielu stronach pliku PDF.
Zamiana PDF na Excel w wersjach 2019, 2016, 2013, 2010 Power Query
W starszych wersjach Excela (innych niż Excel 365) nie mamy możliwości bezpośredniej zmiany pdf na Excel. W tym poradniku przedstawię jak wykonać zamianę PDF na Excel w starszej wersji Excela tj. w wersjach 2019, 2016, 2013, 2010 przy użyciu edytora Power Query.
Pobieranie Danych Ze Strony Internetowej Do Excela – Power Query i Api
Zobacz, jak szybko pobierać dane ze stron www do Excela – od prostego kopiowania, przez Power Query, aż po wykorzystanie API.
do osób, które chcą nauczyć się podstaw Power Query za darmo w mniej niż godzinę.
Darmowy Kurs Power Query dla początkujących