Power Query w Excelu: import, czyszczenie, łączenie i odświeżanie danych
By Mirko Peters · · Updated
Power Query w Excelu importuje i czyści dane bez użycia formuł. Dowiesz się, gdzie go znaleźć, jak działa From Table/Range i Applied Steps, czym się różni scalanie od dołączania, a także jak ładować dane i je odświeżać.
Automatically translated with AI from the original post. Read the English original
Power Query w Excelu to narzędzie na karcie Dane, w grupie Pobieranie i przekształcanie danych, które łączy się z danymi, czyści je i przekształca, a następnie ładuje wynik do arkusza. Kroki, które wykonujesz, są zapisywane i uruchamiane ponownie przy każdym odświeżeniu, dzięki czemu comiesięczne czyszczenie danych sprowadza się do jednego kliknięcia. Ten artykuł jest przeznaczony dla użytkowników Excela, którzy co tydzień kopiują, wklejają i poprawiają te same eksporty i chcą z tym skończyć. Znajdziesz tu informacje, od czego zacząć, jak oczyścić tabelę, jak dołączać i scalać dane, jak je ładować i odświeżać oraz jakie są ograniczenia w wersjach dla komputerów Mac i w przeglądarce.
Najważniejsze wnioski
- Power Query w Excelu znajduje się na karcie Dane, w grupie Pobieranie i przekształcanie danych. Służy do łączenia się z danymi, ich przekształcania i ładowania do arkusza lub modelu danych.
- Każda zmiana jest zapisywana jako krok w obszarze Applied Steps. Oryginalne źródło nie ulega zmianie, a kroki są ponownie wykonywane przy każdym odświeżeniu.
- Dołączanie układa wiersze z tabel o tych samych kolumnach. Scalanie łączy dwie tabele na podstawie pasujących kolumn kluczy, a pasujące kolumny muszą mieć ten sam typ danych.
- Aby ponownie uruchomić kwerendy, użyj polecenia Dane, Odśwież wszystko. W oknie Właściwości połączenia możesz ustawić odświeżanie w określonych odstępach czasu lub przy otwieraniu pliku.
- Excel dla komputerów Mac i Excel dla sieci Web obsługują część funkcji Power Query. Dokumentacja Microsoftu różni się jednak w zakresie tego, co każda z tych wersji pozwala tworzyć, dlatego sprawdź to dla swojej wersji.
Firma Microsoft opisuje cztery etapy: połączenie z danymi, ich przekształcenie, połączenie z innymi źródłami i załadowanie. Oryginalne źródło przez cały czas pozostaje niezmienione. Właśnie to sprawia, że eksperymentowanie jest bezpieczne. Nie da się zepsuć eksportu, od którego zaczynasz.
Czym jest Power Query i gdzie go znaleźć
Power Query to silnik przygotowywania danych z graficznym edytorem. Zgodnie ze stroną Microsoftu o tym, czym jest Power Query, wykonuje on zadania ekstrakcji, przekształcania i ładowania danych, nie ma własnego magazynu i pozostawia wybór miejsca docelowego aplikacji hosta. Excel jest jednym z hostów. Power BI Desktop to kolejny, a obie aplikacje współdzielą ten sam rdzeń edytora. Łączniki i miejsca docelowe mogą się jednak różnić w zależności od hosta, dlatego nie zakładaj, że kwerenda widziana w jednym produkcie wygląda identycznie w drugim.
Lokalizacja zależy od wersji Excela, zgodnie z przeglądem Power Query w Excelu:
- Excel 2016, 2019 i Microsoft 365 dla systemu Windows. Wbudowane. Przejdź do karty Dane i sprawdź grupę Pobieranie i przekształcanie danych.
- Excel 2010 i 2013 dla systemu Windows. Power Query był bezpłatnym dodatkiem z własną kartą wstążki. Firma Microsoft podaje, że jest on wycofywany, choć może nadal działać.
- Excel dla komputerów Mac. Obsługuje odświeżanie danych z kilku źródeł. Nie jest dostępny w programach Excel 2016 i 2019 dla komputerów Mac.
- Excel dla sieci Web. Obsługiwany częściowo. Więcej na ten temat poniżej.
W systemie Windows Microsoft wymienia także jako wymagania .NET Framework 4.7.2 lub nowszy oraz WebView2 Runtime dla konektora sieciowego. Jeśli na służbowym komputerze brakuje tych poleceń, zapytaj dział IT, czy kompilacja jest aktualna, zanim uznasz, że funkcja została usunięta.
Jak używać Power Query w programie Excel: pierwsze zapytanie
Najłatwiej zacząć od danych, które już masz w skoroszycie.
- Kliknij dowolną komórkę w tabeli programu Excel. Jeśli dane są zwykłym zakresem, najpierw naciśnij Ctrl+T, aby zamienić je w tabelę.
- Przejdź do Dane > Pobierz i przekształć dane > Z tabeli/zakresu.
- Otworzy się Edytor Power Query z podglądem danych.
- Wprowadź zmiany (zob. następną sekcję).
- Wybierz Narzędzia główne > Zamknij i załaduj.
Na stronie Microsoft dotyczącej importu z tabeli programu Excel napisano, że wynik domyślnie trafia do nowego arkusza, a oryginalny arkusz nie zmienia się. Jeśli chcesz inne miejsce docelowe, użyj opcji Załaduj do. Zmień nazwę nowego arkusza, aby nikt nie pomylił go ze źródłem.
Aby połączyć się z innym źródłem, użyj Dane > Pobierz dane i wybierz źródło: plik, bazę danych, usługę online lub sieć Web. Po wybraniu zobaczysz podgląd i wybór między Załaduj (bezpośrednio do programu Excel) a Przekształć dane (najpierw otwórz edytor). Moja rada: wybieraj Przekształć dane prawie za każdym razem, nawet gdy uważasz, że dane są czyste. To w edytorze ustawiasz typy kolumn, a błędne typy powodują później większość błędów odświeżania.
Jeśli wolisz zacząć od pustego zapytania, ta sama strona wymienia Dane > Pobierz dane > Z innych źródeł > Puste zapytanie oraz opcję Uruchom Edytor Power Query w menu Pobierz dane.
Edytor prostym językiem
- Podgląd danych pokazuje tabelę i aktualizuje się po każdej zmianie.
- Panel Ustawienia zapytania wyświetla listę ZASTOSOWANE KROKI. Każda wykonana czynność dodaje krok, na przykład Źródło lub Zmieniono typ. Możesz wybrać krok, aby zobaczyć dane w tym punkcie, zmienić krok lub go usunąć.
- Panel Zapytania zawiera wszystkie zapytania w skoroszycie. Otwórzesz go strzałką obok podglądu.
- Edytor zaawansowany pozwala zobaczyć i edytować skrypt bazowy w języku Power Query M. Microsoft zauważa, że każdy krok zbudowany w interfejsie jest zapisywany w języku M. Nie musisz go modyfikować, aby czerpać wartość z tego narzędzia.
Czyszczenie i przekształcanie danych za pomocą kroków
Typowe zadania porządkowe w edytorze: filtrowanie wierszy, usuwanie kolumn, zmiana typów danych, dzielenie kolumny i zastępowanie wartości. Edytor rejestruje każde z nich jako krok. Polecenia znajdują się na wstążce edytora oraz w menu, które pojawia się po kliknięciu prawym przyciskiem nagłówka kolumny. Które z nich widzisz, zależy od typu kolumny, więc zanim będziesz szukać na wstążce, sprawdź menu nagłówka.
Rutyna, która sprawdza się przy większości eksportów:
- Najpierw ustaw typy. Sprawdź ikonę typu obok każdego nagłówka kolumny. Daty, liczby i tekst zachowują się inaczej w kolejnych krokach i podczas scalania.
- Usuń to, czego nie potrzebujesz. Mniej kolumn oznacza mniejszy, szybszy wynik i mniej niespodzianek, gdy źródło się zmieni.
- Filtruj wiersze. Użyj strzałki w nagłówku kolumny, aby usunąć puste wartości lub niechciane wartości.
- Popraw tekst. Zastąp literówki i stare kody, usuń zbędne spacje lub podziel kolumnę, która zawiera dwie informacje.
- Zmień nazwę zapytania w panelu Ustawienia zapytania, abyś mógł je później łatwo znaleźć.
Ponieważ Zastosowane kroki to lista, możesz poprawić wcześniejszy błąd bez zaczynania od nowa. Jeśli krok zakończy się błędem po zmianie źródła, sprawdź pierwszy krok z błędem. Najczęściej wskazuje on kolumnę, której nazwa lub typ się zmienił.
Uwaga dotycząca zmienionych nazw kolumn: strona Microsoft o odświeżaniu zauważa, że nowe kolumny źródła pojawiają się w Power Query dopiero po sprawdzeniu kroku Źródło, a żółty pasek informujący, że podgląd może być nieaktualny, oznacza, że należy odświeżyć lokalną pamięć podręczną. Jeśli nowa kolumna się nie pojawia, najpierw sprawdź te dwie rzeczy.
Łączenie danych: dołączanie i scalanie
Te dwa polecenia wykonują różne zadania, a ludzie często je mylą.
Append dokleja wiersze. Użyj go, gdy masz wiele tabel tego samego typu, na przykład eksport za każdy miesiąc. Microsoft opisuje append jako dołączanie wierszy jednego zapytania do drugiego. Istnieją dwa warianty: append w linii rozbudowuje istniejące zapytanie, a append pośredni tworzy za każdym razem nowe zapytanie.
Merge łączy dwie tabele obok siebie na podstawie pasujących wartości, podobnie jak wyszukiwanie. Strona Microsoftu o scalaniu zapytań opisuje kolejne kroki:
- Upewnij się, że masz co najmniej dwa zapytania z kolumną do dopasowania. Kolumny dopasowujące muszą mieć ten sam typ danych, na przykład Tekst lub Liczba.
- W edytorze przejdź do Narzędzia główne > Scal zapytania. Wynik staje się nowym krokiem w bieżącym zapytaniu. Jeśli zamiast tego chcesz osobne zapytanie, otwórz strzałkę obok przycisku Scal zapytania i wybierz Scal zapytania jako nowe.
- Wybierz tabelę główną i jej kolumnę klucza, a następnie tabelę powiązaną i jej kolumnę klucza. W przypadku kilku kolumn kluczy zaznacz je w tej samej kolejności w obu tabelach.
- Sprawdź liczbę dopasowań pokazaną w oknie. Jeśli jest niższa, niż oczekujesz, kolumny kluczy prawdopodobnie różnią się pisownią, odstępami lub typem.
- Wybierz Typ złączenia (poniżej) i kliknij przycisk OK.
- Pojawi się nowa kolumna. Kliknij ikonę Rozwiń obok jej nagłówka i zaznacz powiązane kolumny, które chcesz dodać.
Typy złączeń według Microsoftu:
- Lewostronne zewnętrzne zachowuje wszystkie wiersze tabeli głównej oraz dopasowania z tabeli powiązanej.
- Prawostronne zewnętrzne zachowuje wszystkie wiersze tabeli powiązanej oraz dopasowania z tabeli głównej.
- Pełne zewnętrzne zachowuje wszystkie wiersze z obu tabel.
- Wewnętrzne zachowuje wyłącznie wiersze, które mają dopasowanie w obu tabelach.
- Lewostronne antyzłączenie zachowuje wiersze tabeli głównej, które nie mają dopasowania. Prawostronne antyzłączenie zachowuje wiersze tabeli powiązanej, które nie mają dopasowania.
- Krzyżowe łączy każdy wiersz tabeli głównej z każdym wierszem tabeli powiązanej.
Kolejność tabel ma znaczenie, ponieważ określenia lewa i prawa zależą od tego, którą tabelę wybrałeś jako pierwszą. Pole wyboru Użyj dopasowania rozmytego do scalenia dopasowuje wartości przybliżone, a Microsoft podaje, że działa ono wyłącznie w kolumnach tekstowych. Jeśli wcześniej korzystałeś z wyszukiwania, najbliższym odpowiednikiem będzie scalenie z lewostronnym zewnętrznym złączeniem, a szybkim sposobem sprawdzenia, które wiersze nie mają dopasowania, jest lewostronne antyzłączenie, na przykład zamówienia bez rekordu klienta.
Microsoft wspomina także o poziomach prywatności. Power Query klasyfikuje każde źródło jako Publiczne, Organizacyjne lub Prywatne i używa tego podziału, aby zapobiegać przypadkowemu łączeniu danych w sposób, który mógłby ujawnić prywatne informacje innemu źródłu. Jeśli scalanie pyta o poziomy prywatności, przeczytaj komunikat, zanim klikniesz dalej.
Załaduj zapytanie i odśwież je
Zamknij edytor poleceniem Narzędzia główne > Zamknij i załaduj. Strona Microsoftu o tworzeniu, ładowaniu i edytowaniu zapytania opisuje te miejsca docelowe:
- Zamknij i załaduj wysyła wynik do arkusza.
- Zamknij i załaduj do pozwala wybrać inne miejsce docelowe, na przykład model danych. W oknie Importuj dane zaznacz Dodaj te dane do modelu danych, jeśli planujesz tworzenie tabel przestawnych na podstawie kilku tabel.
- Jeśli opcja Załaduj do jest wyłączona, użyj polecenia Zamknij i załaduj, a następnie przejdź do Dane > Zapytania i połączenia, kliknij prawym przyciskiem myszy zapytanie na karcie Zapytania i wybierz Załaduj do.
Aby zmienić ustawienia domyślne, przejdź do Plik > Opcje i ustawienia > Opcje zapytania > Ładowanie danych. Możesz użyć standardowych ustawień ładowania lub zdefiniować własne wartości domyślne, aby ładować dane do arkusza lub do modelu danych.
Aby później edytować zapytanie, zaznacz komórkę w jego danych i wybierz Zapytanie > Edytuj albo kliknij prawym przyciskiem myszy zapytanie w obszarze Zapytania i połączenia i wybierz Edytuj.
Odświeżanie
Odświeżenie ponownie uruchamia wszystkie kroki względem bieżącego źródła. Sposoby według strony o odświeżaniu Microsoftu:
- Dane > Odśwież wszystko aktualizuje wszystko w skoroszycie (Ctrl+Alt+F5). Strzałka obok przycisku udostępnia polecenie Odśwież dla wybranego elementu (Alt+F5). Klawisz Esc przerywa odświeżanie.
- Aby włączyć automatyczne odświeżanie, przejdź do Dane > Zapytania i połączenia, otwórz kartę Połączenia, kliknij prawym przyciskiem myszy wybrane połączenie i wybierz Właściwości. Na karcie Użycie możesz ustawić Odświeżaj co (w minutach) oraz Odświeżaj dane podczas otwierania pliku.
- Opcja Włącz odświeżanie w tle na tej samej karcie pozwala kontynuować pracę podczas wykonywania zapytania. Nie jest dostępna dla zapytań OLAP ani dla połączeń zasilających model danych.
Zapisane hasła nie są szyfrowane, a firma Microsoft zaleca, by ich nie zapisywać. Wyczyść opcję Zapisz hasło na karcie Definicja, a program Excel zapyta o hasło jednorazowo w ramach sesji.
Jeśli wysyłasz skoroszyt innej osobie, może ona potrzebować dostępu do źródła danych. Zapytanie, które odczytuje plik na Twoim dysku lub z witryny, do której odbiorca nie ma dostępu, zakończy się błędem na jego komputerze. Umieść źródło danych w lokalizacji udostępnionej lub wyślij załadowane wyniki jako wartości.
Power Query w systemie Mac i w programie Excel dla sieci Web
Obie platformy obsługują część tego narzędzia, ale strony pomocy firmy Microsoft nie są w pełni zgodne.
Excel dla sieci Web. Strona Microsoft mówi, że można importować dane z różnych źródeł, m.in. ze skoroszytu programu Excel, plików Tekst/CSV, XML, JSON, SQL Server, listy w usłudze SharePoint Online i OData, a także z pustej tabeli lub pustego zapytania. Można otworzyć Edytor Power Query (Dane > Pobierz dane > Uruchom Edytor Power Query), załadować dane poleceniem Zamknij i załaduj oraz odświeżyć je poleceniem Dane > Odśwież wszystko. Wyświetlanie i odświeżanie zapytań jest dostępne dla wszystkich subskrybentów Microsoft 365, natomiast więcej funkcji wymaga planu biznesowego lub planu enterprise (E3 i wyższych). Niektóre funkcje wymagają włączenia w przeglądarce plików cookie innych firm.
Excel dla komputerów Mac. Jedna ze stron firmy Microsoft mówi, że zaczynasz od Dane > Pobierz dane > Pobierz dane (Power Query), że kształtowanie danych wymaga usługi Microsoft 365 oraz wersji 16.69 lub nowszej, a polecenie Przekształć dane otwiera edytor. Na tej samej stronie osobna sekcja stwierdza, że tworzenie zapytań w edytorze nie jest dostępne w programie Excel dla komputerów Mac, i opisuje pisanie zapytań w VBA w systemie Windows oraz import modułu na komputerze Mac. Nie potrafię pogodzić tych dwóch stwierdzeń, dlatego przed zbudowaniem na tym procesu przetestuj to na własnym komputerze Mac. Import z SQL Server jest opisany jako dostępny wyłącznie w wersji beta dla uczestników programu Insider.
Kiedy Power Query nie wystarcza
Powiem wprost: Power Query służy do przygotowywania danych, a nie do ich przechowywania. Według firmy Microsoft to aplikacja nadrzędna określa, dokąd trafiają wyniki, a w programie Excel oznacza to arkusz lub model danych. Arkusz ma stały limit wierszy, więc bardzo duży wynik zapytania nie zmieści się na nim. Załaduj go wtedy do modelu danych albo przenieś pracę do bazy danych lub dedykowanego potoku przetwarzania.
Inne ograniczenia, o których warto wiedzieć:
- Zapytania odczytują źródło w momencie uruchomienia. Nie przesyłają na bieżąco zmian do skoroszytu.
- Współpraca jest uciążliwa. Edytowanie tego samego zapytania przez dwie osoby we współdzielonym skoroszycie to zły pomysł. Ustalcie jednego właściciela i nadawajcie zapytaniom czytelne nazwy.
- Jeśli pracujesz z danymi podlegającymi regulacjom, pamiętaj, że skoroszyt łatwo skopiować. Przestrzegaj zasad obowiązujących w Twojej organizacji dotyczących miejsc przechowywania takich plików.
Częste błędy
- Pominięcie typów danych, a potem zdziwienie, dlaczego scalanie nic nie dopasowuje.
- Scalanie kolumn o różnych typach lub z końcowymi spacjami.
- Użycie scalania zamiast dołączania albo odwrotnie.
- Ładowanie wszystkiego do arkusza, podczas gdy lepiej sprawdzi się zapytanie tylko z połączeniem albo model danych.
- Zapisanie hasła do źródła w połączeniu.
- Zapomnienie o odświeżeniu przed wysłaniem raportu.
- Przeniesienie pliku źródłowego i późniejsze dziwienie się, dlaczego zapytanie kończy się błędem.
Powiązane tematy do zgłębienia
- Usuwanie duplikatów w edytorze. Power Query może usunąć duplikaty w tabeli jako krok, który można powtarzać, w przeciwieństwie do jednorazowego polecenia na wstążce.
- Przekształcanie kolumn w wiersze (odwracanie przestawienia). Zamień kolumny miesięcy na wiersze, zanim utworzysz tabelę przestawną. Firma Microsoft wspomina o Power Query właśnie w tym celu.
- Parametry. Firma Microsoft ma stronę o zapytaniach z parametrami, które pozwalają ponownie użyć zapytania z różnymi danymi wejściowymi.
- Język M. Dowiedz się, jak czytać skrypt w Edytorze zaawansowanym, zaczynając od strony Power Query M.
- Power BI. Dowiedz się, jak ten sam edytor działa w Power BI Desktop, gdy Twoje dane przerosną Excela.
Następny krok
Wybierz jeden eksport, który co tydzień czyścisz ręcznie. Zamień go w tabelę, przepuść przez funkcję Z tabeli/zakresu, ustaw typy kolumn i załaduj do nowego arkusza. W kolejnym tygodniu wklej nowy eksport do tabeli i naciśnij Odśwież wszystko. Tak właśnie działa Power Query w Excelu.
Źródła
- Informacje o Power Query w programie Excel (Microsoft Support)
- Czym jest Power Query? (Microsoft Learn)
- Importowanie z tabeli programu Excel (Microsoft Support)
- Tworzenie, ładowanie i edytowanie zapytania w programie Excel (Power Query) (Microsoft Support)
- Dołączanie zapytań (Power Query) (Microsoft Support)
- Scalanie zapytań (Power Query) (Microsoft Support)
- Odświeżanie zewnętrznego połączenia danych w programie Excel (Microsoft Support)
- Korzystanie z Power Query w programie Excel dla sieci Web (Microsoft Support)
- Importowanie i kształtowanie danych w programie Excel dla komputerów Mac (Power Query) (Microsoft Support)