Power Query w Excelu - praktyczny poradnik od importu do scalania danych

Co miesiąc przychodzi ten sam plik z systemu: puste wiersze w środku, wiersz podsumowania na dole, imię i nazwisko w jednej kolumnie, czasem z „Pan” albo „Pani” na początku. Porządkujesz go ręcznie, a za miesiąc robisz to samo od nowa. Power Query w Excelu zapamiętuje każdy krok takiego sprzątania i przy następnym pliku powtarza go za Ciebie.

W tym poradniku przechodzimy cały proces na jednej tabeli z zamówieniami: import do edytora, usuwanie zbędnych wierszy i kolumn, dzielenie imienia i nazwiska, typy danych i załadowanie wyniku z powrotem do Excela. Potem rzeczy, które w Power Query robi się szybciej niż formułami: grupowanie po miesiącach, kolumna warunkowa, scalanie z drugą tabelą wczytaną z pliku CSV i kolumna przestawna.

Najwięcej daje jedna sekcja: ta o zastosowanych krokach. W edytorze nie zadziała Ctrl + Z, ale każdą operację da się później poprawić albo usunąć. Kiedy to zrozumiesz, przestajesz się bać, że coś zepsujesz, i zaczynasz próbować.

W tym poradniku:

1. Jak zaimportować dane do Power Query
2. Edytor Power Query w skrócie
3. Usuwanie pustych wierszy i wiersza podsumowania
4. Pierwszy wiersz jako nagłówki
5. Usuwanie zbędnych kolumn
6. Jak podzielić kolumnę i usunąć zbędny tekst
7. Typy danych w Power Query
8. Załadowanie danych do Excela i odświeżanie
9. Duplikat i odwołanie do zapytania
10. Grupowanie danych w Power Query
11. Filtrowanie danych
12. Jak cofnąć krok w Power Query
13. Kolumna warunkowa
14. Import danych z pliku CSV
15. Scalanie zapytań
16. Kolumna przestawna
17. Podsumowanie

Jak zaimportować dane do Power Query

Przykład to zwykła tabela w Excelu z około setką zamówień: imię i nazwisko, data zamówienia, kwota sprzedaży, identyfikator firmy, e-mail i uwagi. Ma kilka typowych wad pliku wyciągniętego z systemu: podwójny nagłówek, puste wiersze i wiersz z podsumowaniem na końcu.

Żeby wczytać te dane do Power Query, kliknij dowolną komórkę tabeli i wybierz Dane > Z tabeli/zakresu. Polecenie stoi w grupie Pobieranie i przekształcanie danych.

Excel najpierw zamieni zakres na tabelę i zapyta o jej granice. Sprawdź je: pusty wiersz w środku danych przerywa automatyczne zaznaczenie i reszta zamówień zostałaby poza tabelą. W przykładzie trzeba ręcznie wpisać zakres $A$1:$F$106. Po kliknięciu OK otworzy się edytor Power Query z wczytanymi danymi.

Jeśli nie widzisz tego polecenia albo edytor się nie otwiera, zajrzyj do artykułu Co to jest Power Query, gdzie opisuję, w których wersjach Excela go szukać.

Edytor Power Query w skrócie

Edytor otwiera się w osobnym oknie. Po lewej jest lista zapytań, na środku podgląd danych, po prawej panel zastosowanych kroków z każdą operacją, jaką wykonasz na danych. Na górze wstążka z poleceniami do filtrowania, przekształcania i łączenia tabel.

W edytorze pracujesz na całych kolumnach i wierszach, nie na pojedynczych komórkach. Każde polecenie ze wstążki dotyczy zaznaczonych kolumn albo wierszy, które spełniają warunek.

Usuwanie pustych wierszy i wiersza podsumowania

Zacznij od pustych wierszy: Narzędzia główne > Usuń wiersze > Usuń puste wiersze (na zrzucie litera A). To polecenie usuwa tylko wiersze, które są puste we wszystkich kolumnach.

Dlatego wiersz podsumowania na końcu zostaje: ma wartości w części kolumn, więc dla Power Query nie jest pusty. Usuwasz go poleceniem Narzędzia główne > Usuń wiersze > Usuwanie końcowych wierszy (litera B). W okienku wpisz 1 i zatwierdź.

Pierwszy wiersz jako nagłówki

Tabela miała podwójny nagłówek. Górny Power Query wziął za nazwę pierwszej kolumny („Zamówienia”), a pozostałe kolumny dostały nazwy Kolumna1, Kolumna2 i dalej. Prawdziwe nazwy wylądowały w pierwszym wierszu danych.

Wystarczy jedno polecenie: Narzędzia główne > Użyj pierwszego wiersza jako nagłówków. Pierwszy wiersz przechodzi do nagłówków i kolumny mają już właściwe nazwy.

Usuwanie zbędnych kolumn

Kolumna Uwagi nic nie wnosi do zestawienia, więc ją usuwamy. Zaznacz nagłówek kolumny (albo kilku, z wciśniętym Ctrl) i kliknij Narzędzia główne > Usuń kolumny.

Jak podzielić kolumnę i usunąć zbędny tekst

Do zestawienia potrzebne są imię i nazwisko w osobnych kolumnach. Przeszkadza jedna rzecz: kilka osób ma przed nazwiskiem „Pan” albo „Pani”, więc po podziale te słowa trafiłyby do kolumny z imieniem.

Najpierw usuń formy grzecznościowe. Zaznacz kolumnę Imię i Nazwisko i wybierz Narzędzia główne > Zamienianie wartości. W polu „Wartość do znalezienia” wpisz „Pan ” (ze spacją na końcu), pole „Zamień na” zostaw puste. Powtórz to samo dla „Pani ”.

Spacja na końcu ma znaczenie. Bez niej „Pan” zostałoby wycięte także z „Pani”, a z „Pani Agnieszka” zrobiłoby się „i Agnieszka”.

Teraz zaznacz kolumnę i wybierz Podziel kolumnę > Według ogranicznika, a jako ogranicznik wskaż spację. Power Query rozdzieli kolumnę na dwie. Zostaje zmiana nazw: kliknij dwukrotnie nagłówek i wpisz „Imię”, a w drugiej kolumnie „Nazwisko”.

Typy danych w Power Query

Obok nazwy każdej kolumny stoi znaczek typu danych: tekst, liczba, data, waluta. Od typu zależy, które polecenia w ogóle zadziałają. Kolumny z datą zapisaną jako tekst nie przekształcisz na koniec miesiąca, a liczby zapisane jako tekst nie dadzą się zsumować przy grupowaniu.

Typy możesz ustawić hurtem poleceniem Przekształć > Wykryj typ danych, ale Power Query nie zawsze zgaduje dobrze. Pewniej jest ustawić je ręcznie: zaznacz kolumnę i wybierz typ z listy Przekształć > Typ danych. W przykładzie kolumna Sprzedaż dostaje typ Waluta.

Załadowanie danych do Excela i odświeżanie

Dane są uporządkowane, więc można je przenieść do Excela. Przedtem zmień nazwę zapytania z „Tabela1” na coś, co za miesiąc będzie jeszcze zrozumiałe, w przykładzie „Zamówienia”. Nazwę wpisujesz w polu Nazwa w panelu po prawej.

Potem kliknij Zamknij i załaduj, a dane trafią do nowego arkusza. Druga opcja, Zamknij i załaduj do, pozwala wybrać miejsce i formę wyniku.

Dane z Power Query możesz załadować jako:

• tabelę,
• raport tabeli przestawnej,
• wykres przestawny,
• samo połączenie, bez wstawiania danych do arkusza.

Możesz też dodać dane do modelu danych, z którym pracuje Power Pivot.

Po załadowaniu w Excelu pojawi się panel Zapytania i połączenia ze wszystkimi zapytaniami skoroszytu. Prawy przycisk myszy na zapytaniu daje edycję, kopiowanie, zmianę nazwy i odświeżanie.

Tu jest cały sens Power Query. Kiedy w pliku źródłowym przybędzie zamówień, nie powtarzasz żadnego z kroków. Klikasz Odśwież, a Power Query wykonuje je wszystkie jeszcze raz, na nowych danych.

Duplikat i odwołanie do zapytania

Z jednego zapytania często chcesz zrobić dwa różne zestawienia. Masz do wyboru dwie drogi:

• Duplikuj kopiuje zapytanie razem ze wszystkimi krokami. Od tej chwili obie kopie żyją osobno, a poprawka w jednej nie trafia do drugiej.
• Odwołanie tworzy nowe zapytanie, które zaczyna się od wyniku pierwszego. Jeśli poprawisz zapytanie źródłowe, odwołanie dostanie tę poprawkę samo.

Oba polecenia znajdziesz pod prawym przyciskiem myszy na zapytaniu, w Excelu albo w edytorze. W przykładzie tworzę Odwołanie i nazywam je „Grupowanie”.

Grupowanie danych w Power Query

W zapytaniu Grupowanie chcę dostać tabelę z sumą i średnią wartością zamówień w każdym miesiącu.

Najpierw sortuję dane rosnąco po dacie (strzałka przy nagłówku, Sortuj rosnąco). Potem zamieniam każdą datę na ostatni dzień jej miesiąca: zaznaczam kolumnę Data zamówienia i wybieram Przekształć > Data > Miesiąc > Koniec miesiąca. Wszystkie zamówienia z września dostają tę samą datę 30.09, więc da się je zgrupować.

Teraz Narzędzia główne > Grupuj według. W oknie przełącz na Zaawansowane, w górnym polu wybierz kolumnę Data zamówienia, a niżej dodaj dwie agregacje na kolumnie Sprzedaż: sumę i średnią. Wynik to tabela z jednym wierszem na miesiąc.

Filtrowanie danych

Tabelę z poprzedniego kroku zawężam do 2024 roku. Okno filtrowania otwiera strzałka przy nagłówku kolumny, a lista warunków zależy od typu danych. Kolumna z datami ma ich najwięcej: od „jest przed” po „jest w kwartale”.

W przykładzie ustawiam dwa warunki połączone przez „Oraz”: data jest po lub równa 01.01.2024 i przed lub równa 31.12.2024.

⚡ Power Query

Grupowanie, scalanie i kolumny przestawne mają w kursie osobne działy

✓Import z CSV, TXT, PDF, stron WWW i wielu plików naraz

✓Scalanie tabel: lewe, pełne, anty i z agregacją

✓Grupowanie i kolumna przestawna na prawdziwych danych

✓Lekcje wideo: 26 · zadania z rozwiązaniami: 17

Zobacz program kursu

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

Jak cofnąć krok w Power Query

W edytorze Power Query nie ma cofania skrótem Ctrl + Z. Jest coś lepszego: każda operacja zapisuje się jako krok, trochę jak w makrze. Wszystkie kroki widać po prawej, w panelu Zastosowane kroki.

Możesz kliknąć dowolny wcześniejszy krok, obejrzeć dane w tamtym momencie i go zmienić. Krok usuwasz czerwonym krzyżykiem, a poprawiasz ikoną koła zębatego obok jego nazwy. Kiedy koła nie ma, krok edytujesz na pasku formuły nad danymi.

Uważaj przy zmianach w środku listy. Każdy krok pracuje na wyniku poprzedniego, więc jeśli w kroku trzecim zmienisz nazwę kolumny, krok piąty, który się do niej odwołuje, zgłosi błąd. Po każdej takiej zmianie przeklikaj kroki do końca i sprawdź, czy żaden nie kończy się błędem.

Kroki są zapisane w języku M. Jak je czytać, poprawiać ręcznie i pisać własne funkcje, pokazuję w kursie Power Query Zaawansowany.

Kolumna warunkowa

Wracam do zapytania Zamówienia. Chcę dodać kolumnę Rodzaj, w której zamówienie powyżej 10 000 zł dostaje etykietę „VIP”, a pozostałe „Zwykłe”.

Służy do tego Dodaj kolumnę > Kolumna warunkowa. Okno działa jak funkcja JEŻELI (IF) w Excelu, tylko bez pisania formuły: wybierasz kolumnę Sprzedaż, operator „jest większe niż”, wartość 10000 i wynik „VIP”, a w polu „W przeciwnym razie” wpisujesz „Zwykłe”.

Na pasku formuły widać, co Power Query z tego zrobił. To zwykła instrukcja „jeśli, to, w przeciwnym razie” w języku M:

= Table.AddColumn(#"Zmieniono typ3", "Rodzaj", each if [Sprzedaż] > 10000 then "VIP" else "Zwykłe")

Import danych z pliku CSV

Power Query pobiera dane z plików, folderów, baz danych i stron internetowych. Tu potrzebuję listy firm, która leży w pliku CSV. W edytorze klikam prawym przyciskiem myszy w pustym miejscu listy zapytań i wybieram Nowe zapytanie > Plik > Plik tekstowy lub CSV.

W oknie importu sprawdź kodowanie pliku (przy polskich znakach zwykle UTF-8 albo 1250), ogranicznik i sposób wykrywania typów, potem zatwierdź. Pojawi się nowe zapytanie „Firmy” z numerem ID, nazwą, adresem i miastem każdej firmy.

Scalanie zapytań

W zapytaniu Zamówienia jest tylko identyfikator firmy, na przykład „ID1”. Chcę w jego miejsce zobaczyć nazwę firmy i miasto z zapytania Firmy. W Excelu robiłoby się to funkcją WYSZUKAJ.PIONOWO (VLOOKUP), w Power Query służy do tego scalanie.

Najpierw kolumny łączące muszą mieć identyczne wartości. Tu nie mają: w Zamówieniach jest „ID1”, a w Firmach samo „1”. Dla Power Query to dwie różne rzeczy, więc scalenie nie znalazłoby żadnej pary. Zaznaczam kolumnę ID w zapytaniu Firmy i wybieram Przekształć > Format > Dodaj prefiks z wartością „ID”.

Teraz przechodzę do zapytania Zamówienia i wybieram Narzędzia główne > Scal zapytania. W oknie zaznaczam kolumnę Id firmy w Zamówieniach, z listy wybieram zapytanie Firmy i zaznaczam w nim kolumnę ID. Rodzaj sprzężenia: lewe zewnętrzne, czyli wszystkie zamówienia i pasujące do nich firmy.

Power Query dodaje kolumnę Firmy, w której każda komórka zawiera całą dopasowaną tabelę. Rozwijasz ją przyciskiem ze strzałkami w nagłówku tej kolumny.

W oknie rozwijania zaznaczam tylko to, czego potrzebuję: Nazwa firmy i Miasto.

Kolumna przestawna

Kolumna przestawna zamienia wartości z jednej kolumny na nagłówki nowych kolumn. Polecenie odwrotne, anulowanie przestawienia, robi z szerokiej tabeli długą listę. Oba przydają się przy każdym zestawieniu, które trzeba obrócić.

Chcę zobaczyć sprzedaż każdej firmy w każdym miesiącu. Tworzę nowe zapytanie Zestawienie jako odwołanie do Zamówień i zostawiam w nim trzy kolumny: Data zamówienia, Sprzedaż i Nazwa firmy. Najszybciej zaznaczyć te trzy i wybrać Usuń kolumny > Usuń inne kolumny.

Daty zamieniam na nazwy miesięcy: Przekształć > Data > Miesiąc > Nazwa miesiąca. Potem zaznaczam kolumnę Nazwa firmy i wybieram Przekształć > Kolumna przestawna. Jako kolumnę wartości podaję Sprzedaż, czyli tę, która ma się sumować. Wynik: miesiące w wierszach, firmy w kolumnach, sprzedaż na przecięciu.

Dwie rzeczy, które widać na wyniku. Puste skrzyżowania, czyli miesiące bez zamówień danej firmy, mają wartość null. A miesiące ułożyły się w przypadkowej kolejności. Żeby szły po kolei, wróć do kroku przed zamianą dat na nazwy i posortuj tam dane po dacie rosnąco.

I jedna rzecz, której na wyniku nie widać. Nazwa miesiąca nie zawiera roku, więc lipiec 2024 i lipiec 2025 lądują w tym samym wierszu i sumują się razem. Jeśli dane obejmują więcej niż rok, zamiast nazwy miesiąca użyj końca miesiąca, tak jak przy grupowaniu.

Podsumowanie

Power Query w Excelu robi raz to, co dotąd trzeba było robić ręcznie przy każdym nowym pliku, a potem powtarza to jednym odświeżeniem. Na tej jednej tabeli najłatwiej pomylić się w sześciu miejscach:

• Granice tabeli przy imporcie. Pusty wiersz w środku danych ucina zaznaczenie i część rekordów nie trafia do Power Query.
• Usuń puste wiersze nie ruszy wiersza sumy. Usuwa tylko wiersze puste we wszystkich kolumnach.
• Typ danych przed grupowaniem i scalaniem. Data zapisana jako tekst nie zamieni się na koniec miesiąca, a liczba jako tekst nie zsumuje się.
• Scalanie wymaga identycznych wartości. „ID1” i „1” nie znajdą się nawzajem. Power Query rozróżnia też wielkość liter, więc „id1” i „ID1” to dla niego dwie różne wartości.
• Zmiana wcześniejszego kroku może zepsuć następne. Po każdej poprawce w środku listy przejrzyj kroki do końca.
• Nazwa miesiąca nie ma roku. W kolumnie przestawnej sumuje te same miesiące z różnych lat.

To tylko część możliwości Power Query. Dane pobierzesz też ze strony internetowej, z pliku PDF albo z całego folderu plików naraz, a ścieżkę do źródła da się ustawić tak, żeby zmieniała się sama.

Pliki z tego poradnika pobierzesz poniżej, więc wszystkie kroki możesz powtórzyć na tych samych danych.

⚡ Power Query

Przećwicz Power Query na plikach z kursu, nie na jednym przykładzie

✓Pliki do pobrania przy każdej lekcji

✓Zadania z rozwiązaniami: 17

✓Certyfikat ukończenia

✓Gwarancja zwrotu: 30 dni

Zobacz kurs

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy