Power Query: Importuj i przekształcaj dane jak profesjonalista

Jeśli co tydzień spędzasz godziny na pobieraniu plików CSV, usuwaniu pustych wierszy, formatowaniu dat i pisaniu złożonych, zagnieżdżonych formuł tylko po to, by przygotować dane do analizy, pracujesz ciężej, niż to konieczne. Poznaj Power Query — najpotężniejsze narzędzie do automatyzacji danych wbudowane bezpośrednio w program Microsoft Excel.

Power Query, często określane jako "Pobieranie i przekształcanie" (Get & Transform Data), pozwala na połączenie się z niemal każdym źródłem danych, ich oczyszczenie i przekształcenie, a następnie załadowanie do arkusza. Co w tym najlepsze? Narzędzie to rejestruje Twoje kroki. Gdy następnym razem otrzymasz nowe dane, nie musisz powtarzać ręcznej pracy; po prostu klikasz Odśwież.

W tym kompleksowym przewodniku dowiemy się, czym jest Power Query, jak poruszać się po jego interfejsie i omówimy praktyczny przykład przekształcenia zabałaganionego zestawu danych w czyste informacje gotowe do analizy.

Czym jest Power Query?

Power Query to silnik do łączenia i przygotowywania danych. W świecie zarządzania bazami danych proces ten jest znany jako ETL: Extract, Transform, and Load (Ekstrakcja, Transformacja i Ładowanie).

• Ekstrakcja (Extract): Pobieranie danych z różnych źródeł (skoroszyty Excela, pliki CSV i tekstowe, strony internetowe, bazy danych SQL, a nawet usługi w chmurze).
• Transformacja (Transform): Czyszczenie i kształtowanie danych. Obejmuje to filtrowanie wierszy, zmianę typów danych, dzielenie tekstu, scalanie tabel i tworzenie kolumn obliczeniowych.
• Ładowanie (Load): Wyprowadzanie uporządkowanych danych bezpośrednio do tabeli Excela, tabeli przestawnej lub Modelu danych Power Pivot.

Tradycyjnie użytkownicy Excela polegali na kombinacji funkcji takich jak TRIM, PROPER, SUBSTITUTE i VLOOKUP w połączeniu z ręcznym kopiowaniem i wklejaniem, aby poradzić sobie z tymi zadaniami. Power Query zastępuje ten żmudny przepływ pracy wizualnym, przyjaznym dla użytkownika interfejsem.

Dlaczego warto nauczyć się Power Query

Jeśli nadal zastanawiasz się nad nauką nowego narzędzia w Excelu, oto dlaczego opanowanie Power Query jest przełomem dla Twojej produktywności:

• Nie wymaga kodowania: Chociaż Power Query działa w oparciu o język formuł o nazwie "M", możesz wykonać 95% przekształceń danych, używając tylko przycisków na wstążce i menu prawego przycisku myszy.
• Automatyzacja bez VBA: Nie musisz umieć pisać makr, aby automatyzować powtarzalne zadania. Power Query zapamiętuje Twoje Zastosowane kroki (Applied Steps) i automatycznie wdraża je do nowych danych.
• Obsługa dużych zbiorów danych (Big Data): Excel ma fizyczny limit 1 048 576 wierszy. Power Query może łączyć się ze zbiorami danych liczącymi miliony wierszy, agregować dane w tle i ładować tylko podsumowane wyniki do arkusza kalkulacyjnego.
• Edycja nieniszcząca: Power Query nigdy nie zmienia oryginalnego źródła danych. Tworzy połączenie, odczytuje dane i wypluwa nową, oczyszczoną kopię.

Jak zacząć: Edytor Power Query

Aby uzyskać dostęp do Power Query, otwórz pusty skoroszyt Excela i przejdź do karty Dane (Data) na Wstążce. Po lewej stronie poszukaj grupy Pobieranie i przekształcanie danych (Get & Transform Data).

W tym miejscu możesz kliknąć Pobierz dane (Get Data), aby zobaczyć menu rozwijane dostępnych źródeł danych. Po wybraniu pliku i kliknięciu "Przekształć dane" (Transform Data), Excel otworzy Edytor Power Query w nowym oknie. Interfejs ten składa się z czterech głównych obszarów:

1. Wstążka: Znajduje się u góry i zawiera karty takie jak Narzędzia główne (Home), Przekształć (Transform), Dodaj kolumnę (Add Column) i Widok (View). To tutaj znajdują się wszystkie Twoje narzędzia.
2. Okienko Zapytania (Queries): Znajduje się po lewej stronie i wyświetla wszystkie aktywne połączenia danych w Twoim skoroszycie.
3. Podgląd danych: Środkowa siatka wyświetlająca podgląd na żywo tego, jak wyglądają Twoje dane w aktualnie wybranym kroku.
4. Okienko Ustawienia zapytania (Zastosowane kroki): Znajduje się po prawej stronie. To najważniejsza część edytora. Za każdym razem, gdy coś zmienisz, Power Query rejestruje to tutaj jako sekwencyjny krok. Możesz te kroki usuwać, zmieniać ich kolejność lub edytować w dowolnym momencie.

Przewodnik krok po kroku: Czyszczenie zabałaganionych danych sprzedaży

Spójrzmy na praktyczny, rzeczywisty przykład. Wyobraź sobie, że eksportujesz cotygodniowy raport sprzedaży z firmowego systemu CRM. Surowy eksport jest zabałaganiony, zawiera niepotrzebne nagłówki, połączone ciągi tekstowe i niespójne formatowanie.

Oto próbka naszych surowych, nieuporządkowanych danych:

Eksport systemowy: Raport sprzedaży Q3	Kolumna2	Kolumna3
Wygenerowano: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Gdybyśmy użyli tradycyjnych formuł, musielibyśmy wykorzystać funkcje takie jak LEFT, RIGHT, FIND i VALUE do wyodrębnienia nazwisk i naprawienia liczb. Zamiast tego użyjmy Power Query.

Krok 1: Import danych

Zapisz te zabałaganione dane jako plik CSV lub Excel. Otwórz nowy skoroszyt Excela, przejdź do Dane > Pobierz dane > Z pliku (Data > Get Data > From File) i wybierz swój plik. Gdy pojawi się okno podglądu, kliknij Przekształć dane (Transform Data). Otworzy się Edytor Power Query.

Krok 2: Usuwanie górnych wierszy i promowanie nagłówków

Pierwsze dwa wiersze naszych danych to metadane z systemu, a nie rzeczywiste rekordy. Musimy się ich pozbyć.

• Na karcie Narzędzia główne (Home) kliknij Usuń wiersze > Usuń górne wiersze (Remove Rows > Remove Top Rows). Wpisz "2" i kliknij OK.
• Teraz nasze właściwe nagłówki kolumn znajdują się w pierwszym wierszu. Kliknij przycisk Użyj pierwszego wiersza jako nagłówków (Use First Row as Headers) na karcie Narzędzia główne. Błyskawicznie "Rep_ID_Name", "Order_Date" i "Revenue" stają się Twoimi oficjalnymi tytułami kolumn.

Krok 3: Dzielenie kolumny z nazwą

Kolumna "Rep_ID_Name" zawiera zarówno numer identyfikacyjny, jak i nazwisko pracownika oddzielone myślnikiem.

• Kliknij prawym przyciskiem myszy nagłówek kolumny "Rep_ID_Name".
• Wybierz Podziel kolumnę > Według ogranicznika (Split Column > By Delimiter).
• Wybierz opcję "Niestandardowy" (Custom) i wpisz myślnik ( - ). Kliknij OK.
• Power Query dzieli dane na dwie kolumny. Możesz teraz dwukrotnie kliknąć nowe nagłówki kolumn, aby zmienić ich nazwy na "Rep_ID" i "Rep_Name".

Aby usunąć znaki podkreślenia w nazwisku Boba (Bob_Jones), kliknij prawym przyciskiem myszy kolumnę Rep_Name, wybierz Zamień wartości (Replace Values), wpisz podkreślenie (_) w polu "Wartość do znalezienia", a pole "Zamień na" pozostaw puste lub wstaw w nim spację. Kliknij OK.

Krok 4: Standaryzacja typów danych

Zauważasz, że nasze daty i przychody mają zupełnie różne formaty? Power Query ułatwia ich standaryzację.

• Kliknij małą ikonę po lewej stronie nagłówka kolumny "Order_Date" (prawdopodobnie widnieje tam napis "ABC" lub "Dowolny" / "Any"). Zmień to na Data (Date). Power Query automatycznie przetłumaczy różne formaty na ujednolicony standard daty.
• Kliknij ikonę obok "Revenue" i zmień typ na Waluta (Currency) lub Liczba dziesiętna. Operacja ta usunie symbole dolara i sformatuje wszystko w przejrzysty sposób.

Krok 5: Dodawanie kolumny warunkowej

Załóżmy, że chcemy skategoryzować sprzedaż powyżej 1000 USD jako "Wysoka wartość". Zamiast pisać złożoną funkcję IF w Excelu, taką jak =IF(C2>=1000, "High Value", "Standard"), możemy użyć interfejsu Power Query.

Przejdź na kartę Dodaj kolumnę (Add Column) i kliknij Kolumna warunkowa (Conditional Column). Ustaw reguły: Jeśli [Revenue] jest większe lub równe 1000, wynikiem będzie "High Value", w przeciwnym razie "Standard". W tle Power Query generuje następujący kod M dla tego kroku:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Scalanie zapytań: Nowoczesna alternatywa dla VLOOKUP

Jednym z najczęstszych zadań w analizie danych jest łączenie tabel. Jeśli masz osobną tabelę zawierającą region dla każdego przedstawiciela handlowego, prawdopodobnie sięgniesz po nasz kompletny przewodnik po funkcji VLOOKUP, aby połączyć te dane.

Jednak uruchamianie tysięcy formuł VLOOKUP lub kombinacji INDEX i MATCH może drastycznie spowolnić Twój skoroszyt. W Power Query używa się funkcji Scalanie zapytań (Merge Queries).

Wystarczy zaimportować obie tabele do Power Query, wybrać główną tabelę sprzedaży i kliknąć Scal zapytania na karcie Narzędzia główne. Wybierz drugą tabelę (tabelę z regionami), kliknij pasującą kolumnę w obu tabelach (np. "Rep_ID") i kliknij OK. Power Query wykona odpowiednik ultraszybkiego VLOOKUP w zaledwie kilka sekund, niezależnie od tego, czy masz dziesięć, czy dziesięć milionów wierszy.

Anulowanie przestawienia kolumn: Tajna broń Power Query

Często otrzymujesz dane, które są już pogrupowane w strukturę przypominającą tabelę przestawną (na przykład miesiące ułożone w kolumnach: Jan, Feb, Mar, Apr). Chociaż jest to łatwe do odczytania dla ludzi, to jest to fatalne rozwiązanie do tworzenia wykresów lub tabel przestawnych w dalszej pracy.

Wybierz kolumny identyfikujące (np. Rep Name), kliknij prawym przyciskiem myszy na nagłówek i wybierz Anuluj przestawienie innych kolumn (Unpivot Other Columns). Power Query natychmiast przekształci Twoje szerokie, krzyżowe dane w płaski układ tabelaryczny z nowymi kolumnami "Atrybut" (Miesiąc) i "Wartość" (Sprzedaż). Wykonanie tego za pomocą standardowych formuł Excela jest niemal niemożliwe, co czyni to polecenie jedną z najbardziej docenianych funkcji Power Query.

Ładowanie przekształconych danych

Gdy Twoje dane będą już idealnie czyste, czas odesłać je z powrotem do Excela.

Na karcie Narzędzia główne kliknij Zamknij i załaduj (Close & Load). Domyślnie spowoduje to załadowanie przekształconych danych do zupełnie nowej, zielonej Tabeli Excela w nowym arkuszu. Jeśli wolisz wysłać dane prosto do fazy analizy, możesz kliknąć strzałkę w dół, wybrać Zamknij i załaduj do... (Close & Load To...) i w zamian wybrać Raport w formie tabeli przestawnej. Jeśli potrzebujesz przypomnienia na temat budowania takich podsumowań, sprawdź nasz poradnik o tworzeniu tabel przestawnych dla początkujących.

Magia przycisku "Odśwież"

Prawdziwa potęga Power Query staje się widoczna w następnym tygodniu, gdy otrzymasz nowy, surowy eksport danych o sprzedaży. Nie powtarzaj powyższych kroków!

Wystarczy nadpisać stary plik CSV nowym (zachowaj dokładnie taką samą nazwę pliku i lokalizację w folderze). Następnie otwórz skoroszyt Excela, kliknij prawym przyciskiem myszy w dowolnym miejscu w swojej wyczyszczonej tabeli danych i kliknij Odśwież.

Power Query sięgnie po ten plik, ponownie zastosuje każdy pojedynczy krok — usuwając wiersze, promując nagłówki, dzieląc kolumny, zamieniając tekst, sprawdzając warunki i scalając tabele — po czym zaktualizuje wynik końcowy w ułamku sekundy. Jest to kluczowy element przepływów pracy związanych z automatyzacją w Excelu.

Upraszczanie pracy z danymi dzięki sztucznej inteligencji

Choć Power Query doskonale radzi sobie ze strukturalnymi przekształceniami, czasami możesz potrzebować specyficznej logiki warunkowej lub złożonego analizowania tekstu, co wymaga zaawansowanych formuł Excela lub niestandardowego kodu M. Zamiast przeszukiwać fora w poszukiwaniu odpowiedzi, możesz wykorzystać sztuczną inteligencję.

Jeśli masz problemy z napisaniem idealnej kalkulacji dla niestandardowej kolumny, GPTExcel będzie doskonałym asystentem. Po prostu opisz, co chcesz osiągnąć, w prostym języku — na przykład: "Potrzebuję formuły do wyodrębnienia tylko liczb z mieszanego ciągu tekstowego" — a GPTExcel błyskawicznie wygeneruje poprawną formułę lub kod M. Połączenie Power Query i AI do czyszczenia danych daje Ci niepowstrzymany zestaw narzędzi do ich analizy.

Często zadawane pytania (FAQ)

Czy Power Query modyfikuje mój oryginalny plik z danymi?

Nie. Power Query tworzy jednokierunkowe połączenie ze źródłem danych. Odczytuje je, stosuje przekształcenia w pamięci i wyprowadza nowy wynik w Excelu. Twój oryginalny plik CSV, baza danych lub skoroszyt pozostają całkowicie nietknięte i bezpieczne.

Czy mogę używać Power Query na Macu?

Tak, Microsoft znacząco ulepszył obsługę Power Query w Excelu dla komputerów Mac. Chociaż wersja na Maca tradycyjnie nie posiadała niektórych zaawansowanych łączników i funkcji interfejsu dostępnych w systemie Windows, w nowoczesnych wersjach Microsoft 365 można teraz płynnie łączyć się z lokalnymi plikami i bazami danych oraz odświeżać istniejące zapytania.

Jaka jest różnica między Scalaniem a Dołączaniem w Power Query?

Scalanie (Merge) jest odpowiednikiem VLOOKUP lub kombinacji INDEX i MATCH. Służy do dodawania nowych kolumn danych poprzez dopasowanie wspólnego identyfikatora między dwiema tabelami. Dołączanie (Append) przypomina kopiowanie i wklejanie danych na dole arkusza. Używa się go do łączenia tabel i układania ich jedna pod drugą, dodając nowe wiersze (np. łącząc zestawienie sprzedaży ze stycznia i z lutego).

Dlaczego odświeżanie Power Query się nie powiodło?

Najczęstszym powodem niepowodzenia odświeżania zapytania jest to, że plik źródłowy został przeniesiony, usunięty lub zmieniono jego nazwę. Innym częstym problemem jest zmiana nagłówka kolumny w surowych danych (np. "Revenue" zostało zmienione przez system na "Total Revenue"). Możesz to naprawić, otwierając Edytor Power Query, przechodząc do okienka Zastosowane kroki i aktualizując krok Źródło (Source) lub zmieniając nazwę kolumny bezpośrednio w kroku modyfikującym strukturę.