Najlepsze rozwiązania dotyczące pracy z dodatekem Power Query

Ten artykuł zawiera kilka wskazówek i wskazówek, aby jak najlepiej wykorzystać środowisko uzdatniania danych w dodatku Power Query.

Wybieranie odpowiedniego łącznika

Dodatek Power Query oferuje ogromną liczbę łączników danych. Te łączniki wahają się od źródeł danych, takich jak TXT, CSV i Excel, do baz danych, takich jak Microsoft SQL Server, oraz popularnych usług SaaS, takich jak Microsoft Dynamics 365 i Salesforce. Jeśli źródło danych nie jest widoczne w oknie Pobieranie danych, zawsze możesz użyć łącznika ODBC lub OLEDB, aby nawiązać połączenie ze źródłem danych.

Użycie najlepszego łącznika dla zadania zapewni najlepsze środowisko i wydajność. Na przykład użycie łącznika programu SQL Server zamiast łącznika ODBC podczas nawiązywania połączenia z bazą danych programu SQL Server nie tylko zapewnia znacznie lepsze środowisko pobierania danych , ale łącznik programu SQL Server oferuje również funkcje, które mogą poprawić środowisko i wydajność, takie jak składanie zapytań. Aby dowiedzieć się więcej na temat składania zapytań, przejdź do sekcji Przegląd oceny zapytań i składania zapytań w dodatku Power Query.

Każdy łącznik danych jest zgodny ze standardowym środowiskiem, jak wyjaśniono w temacie Pobieranie danych. To ustandaryzowane środowisko ma etap o nazwie Wersja zapoznawcza danych. Na tym etapie otrzymasz okno przyjazne dla użytkownika, aby wybrać dane, które mają zostać pozyskane ze źródła danych, jeśli łącznik go umożliwia, oraz prosty podgląd danych tych danych. Możesz nawet wybrać wiele zestawów danych ze źródła danych za pomocą okna Nawigator , jak pokazano na poniższej ilustracji.

Uwaga

Aby wyświetlić pełną listę dostępnych łączników w dodatku Power Query, przejdź do Połączenie or w dodatku Power Query.

Filtruj wcześnie

Zawsze zaleca się filtrowanie danych we wczesnych etapach zapytania lub jak najszybciej. Niektóre łączniki będą korzystać z filtrów przez składanie zapytań, zgodnie z opisem w temacie Przegląd oceny zapytań i składania zapytań w dodatku Power Query. Najlepszym rozwiązaniem jest również odfiltrowanie wszelkich danych, które nie są istotne w Twoim przypadku. Pozwoli to lepiej skoncentrować się na zadaniu, wyświetlając tylko dane, które są istotne w sekcji podglądu danych.

Możesz użyć menu filtru automatycznego, które wyświetla odrębną listę wartości znalezionych w kolumnie, aby wybrać wartości, które chcesz zachować lub odfiltrować. Możesz również użyć paska wyszukiwania, aby ułatwić znalezienie wartości w kolumnie.

Możesz również skorzystać z filtrów specyficznych dla typu, takich jak W poprzednim dla kolumny daty, daty/godziny lub nawet strefy czasowej daty.

Te filtry specyficzne dla typu mogą pomóc w utworzeniu filtru dynamicznego, który zawsze pobiera dane z poprzedniej x liczby sekund, minut, godzin, dni, tygodni, miesięcy, kwartałów lub lat, jak pokazano na poniższej ilustracji.

Uwaga

Aby dowiedzieć się więcej na temat filtrowania danych na podstawie wartości z kolumny, przejdź do pozycji Filtruj według wartości.

Wykonywanie kosztownych operacji ostatnio

Niektóre operacje wymagają odczytania pełnego źródła danych w celu zwrócenia jakichkolwiek wyników i w związku z tym podgląd w Edytor Power Query będzie powolny. Jeśli na przykład wykonasz sortowanie, możliwe, że na końcu danych źródłowych znajduje się kilka pierwszych posortowanych wierszy. Aby więc zwrócić wszystkie wyniki, operacja sortowania musi najpierw odczytać wszystkie wiersze.

Inne operacje (takie jak filtry) nie muszą odczytywać wszystkich danych przed zwróceniem żadnych wyników. Zamiast tego działają one na danych w sposób nazywany "przesyłaniem strumieniowym". Dane "strumienie" według i wyniki są zwracane po drodze. W Edytor Power Query takie operacje muszą tylko odczytywać wystarczająco dużo danych źródłowych, aby wypełnić wersję zapoznawczą.

Jeśli to możliwe, najpierw wykonaj takie operacje przesyłania strumieniowego i wykonaj kolejne droższe operacje. Pomoże to zminimalizować czas oczekiwania na renderowanie podglądu za każdym razem, gdy dodasz nowy krok do zapytania.

Tymczasowo pracuj z podzbiorem danych

Jeśli dodanie nowych kroków do zapytania w Edytor Power Query działa wolno, rozważ najpierw wykonanie operacji "Zachowaj pierwsze wiersze" i ograniczenie liczby wierszy, względem których pracujesz. Następnie po dodaniu wszystkich potrzebnych kroków usuń krok "Zachowaj pierwsze wiersze".

Używanie prawidłowych typów danych

Niektóre funkcje w dodatku Power Query są kontekstowe dla typu danych wybranej kolumny. Na przykład podczas wybierania kolumny daty dostępne opcje w grupie kolumny Data i godzina w menu Dodaj kolumnę będą dostępne. Jeśli jednak kolumna nie ma zestawu typów danych, te opcje będą wyszarzone.

Podobna sytuacja występuje w przypadku filtrów specyficznych dla typu, ponieważ są one specyficzne dla niektórych typów danych. Jeśli kolumna nie ma zdefiniowanego poprawnego typu danych, te filtry specyficzne dla typu nie będą dostępne.

Ważne jest, aby zawsze pracować z odpowiednimi typami danych dla kolumn. Podczas pracy ze ustrukturyzowanymi źródłami danych, takimi jak bazy danych, informacje o typie danych zostaną przeniesione ze schematu tabeli znalezionego w bazie danych. Jednak w przypadku źródeł danych bez struktury, takich jak PLIKI TXT i CSV, ważne jest, aby ustawić poprawne typy danych dla kolumn pochodzących z tego źródła danych. Domyślnie dodatek Power Query oferuje automatyczne wykrywanie typów danych dla źródeł danych bez struktury. Więcej informacji na temat tej funkcji i jej możliwości można znaleźć w artykule Typy danych.

Uwaga

Aby dowiedzieć się więcej o znaczeniu typów danych i sposobie ich pracy, zobacz Typy danych.

Eksplorowanie danych

Przed rozpoczęciem przygotowywania danych i dodawania nowych kroków przekształcania zalecamy włączenie narzędzi profilowania danych dodatku Power Query w celu łatwego odnajdywania informacji o danych.

Te narzędzia profilowania danych pomagają lepiej zrozumieć dane. Narzędzia udostępniają małe wizualizacje, które pokazują informacje na podstawie kolumny, takie jak:

• Jakość kolumn — udostępnia mały wykres słupkowy i trzy wskaźniki z reprezentacją liczby wartości w kolumnie poniżej kategorii prawidłowych, błędów lub pustych wartości.
• Rozkład kolumn — udostępnia zestaw wizualizacji pod nazwami kolumn, które prezentują częstotliwość i rozkład wartości w każdej kolumnie.
• Profil kolumny — zapewnia bardziej szczegółowy widok kolumny i skojarzonych z nią statystyk.

Możesz również wchodzić w interakcje z tymi funkcjami, co pomoże Ci przygotować dane.

Uwaga

Aby dowiedzieć się więcej na temat narzędzi profilowania danych, zobacz Narzędzia profilowania danych.

Dokumentowanie pracy

Zalecamy dokumentowanie zapytań przez zmianę nazwy lub dodanie opisu do kroków, zapytań lub grup zgodnie z potrzebami.

Podczas gdy dodatek Power Query automatycznie tworzy nazwę kroku w okienku zastosowanych kroków, możesz również zmienić nazwę kroków lub dodać opis do dowolnego z nich.

Uwaga

Aby dowiedzieć się więcej o wszystkich dostępnych funkcjach i składnikach znajdujących się w okienku zastosowanych kroków, przejdź do sekcji Korzystanie z listy Zastosowane kroki.

Podejście modułowe

Całkowicie możliwe jest utworzenie pojedynczego zapytania zawierającego wszystkie przekształcenia i obliczenia, które mogą być potrzebne. Jeśli jednak zapytanie zawiera dużą liczbę kroków, dobrym pomysłem może być podzielenie zapytania na wiele zapytań, w których jedno zapytanie odwołuje się do drugiego. Celem tego podejścia jest uproszczenie i rozdzielenie faz transformacji na mniejsze elementy, aby ułatwić ich zrozumienie.

Załóżmy na przykład, że masz zapytanie z dziewięcioma krokami pokazanymi na poniższej ilustracji.

To zapytanie można podzielić na dwa w kroku Scal z tabelą Ceny. Dzięki temu łatwiej jest zrozumieć kroki, które zostały zastosowane do zapytania sprzedaży przed scaleniem. Aby wykonać tę operację, kliknij prawym przyciskiem myszy krok Scal z tabelą Ceny i wybierz opcję Wyodrębnij poprzedni .

Następnie zostanie wyświetlony monit z wyświetlonym oknom dialogowym, aby nadać nowemu zapytaniu nazwę. Spowoduje to efektywne podzielenie zapytania na dwa zapytania. Jedno zapytanie będzie miało wszystkie zapytania przed scaleniem. Inne zapytanie będzie miało początkowy krok, który będzie odwoływać się do nowego zapytania, oraz pozostałe kroki, które były wykonywane w oryginalnym zapytaniu z tabeli Scalanie z cenami w dół.

Możesz również użyć funkcji odwoływania się do zapytań w miarę dopasowania. Ale dobrym pomysłem jest utrzymanie zapytań na poziomie, który nie wydaje się trudne na pierwszy rzut oka przy użyciu tak wielu kroków.

Uwaga

Aby dowiedzieć się więcej na temat odwoływania się do zapytań, przejdź do sekcji Omówienie okienka zapytań.

Utwórz grupy

Doskonałym sposobem organizowania pracy jest wykorzystanie grup w okienku zapytań.

Jedynym celem grup jest ułatwienie organizowania pracy, służąc jako foldery dla zapytań. Możesz tworzyć grupy w grupach, jeśli kiedykolwiek musisz. Przenoszenie zapytań między grupami jest tak proste, jak przeciąganie i upuszczanie.

Spróbuj podać grupom znaczącą nazwę, która ma sens dla Ciebie i Twojego przypadku.

Uwaga

Aby dowiedzieć się więcej o wszystkich dostępnych funkcjach i składnikach znajdujących się w okienku zapytań, przejdź do sekcji Omówienie okienka zapytań.

Zapytania sprawdzające przyszłość

Upewnij się, że utworzono zapytanie, które nie będzie miało żadnych problemów podczas przyszłego odświeżania, jest priorytetem. W dodatku Power Query istnieje kilka funkcji, które umożliwiają odporność zapytania na zmiany i możliwość odświeżania nawet w przypadku zmiany niektórych składników źródła danych.

Najlepszym rozwiązaniem jest zdefiniowanie zakresu zapytania co do tego, co należy zrobić, i jakie należy uwzględnić pod względem struktury, układu, nazw kolumn, typów danych i dowolnego innego składnika, który należy wziąć pod uwagę w zakresie.

Oto kilka przykładów przekształceń, które mogą pomóc w wprowadzania zapytań odpornych na zmiany:

• Jeśli zapytanie ma dynamiczną liczbę wierszy z danymi, ale stałą liczbę wierszy, które służą jako stopka, która powinna zostać usunięta, możesz użyć funkcji Usuń dolne wiersze .

  Uwaga

  Aby dowiedzieć się więcej na temat filtrowania danych według pozycji wiersza, przejdź do pozycji Filtruj tabelę według pozycji wiersza.

• Jeśli zapytanie ma dynamiczną liczbę kolumn, ale musisz wybrać tylko określone kolumny z zestawu danych, możesz użyć funkcji Wybierz kolumny .

  Uwaga

  Aby dowiedzieć się więcej na temat wybierania lub usuwania kolumn, przejdź do pozycji Wybierz lub usuń kolumny.

• Jeśli zapytanie ma dynamiczną liczbę kolumn i musisz cofnąć przestawienie tylko podzbioru kolumn, możesz użyć funkcji przestawności tylko wybranych kolumn .

  Uwaga

  Aby dowiedzieć się więcej o opcjach co do anulowania przestawiania kolumn, przejdź do sekcji Co przestawne kolumny.

• Jeśli zapytanie zawiera krok, który zmienia typ danych kolumny, ale niektóre komórki dają błędy, ponieważ wartości nie są zgodne z żądanym typem danych, możesz usunąć wiersze, które przyniosły wartości błędów.

  Uwaga

  Aby uzyskać więcej informacji na temat pracy z błędami i radzenia sobie z nimi, przejdź do tematu Radzenie sobie z błędami.

Używanie parametrów

Tworzenie zapytań dynamicznych i elastycznych to najlepsze rozwiązanie. Parametry w dodatku Power Query ułatwiają tworzenie bardziej dynamicznych i elastycznych zapytań. Parametr służy jako sposób łatwego przechowywania wartości i zarządzania nią, które mogą być ponownie używane na wiele różnych sposobów. Jednak jest ona częściej używana w dwóch scenariuszach:

• Argument kroku — możesz użyć parametru jako argumentu wielu przekształceń opartych na interfejsie użytkownika.

  

• Argument funkcji niestandardowej — możesz utworzyć nową funkcję na podstawie zapytania i parametry odwołania jako argumenty funkcji niestandardowej.

  

Główne zalety tworzenia i używania parametrów to:

• Scentralizowany widok wszystkich parametrów za pomocą okna Zarządzanie parametrami .

  

• Ponowne użycie parametru w wielu krokach lub zapytaniach.

• Sprawia, że tworzenie funkcji niestandardowych jest proste i łatwe.

Można nawet użyć parametrów w niektórych argumentach łączników danych. Można na przykład utworzyć parametr nazwy serwera podczas nawiązywania połączenia z bazą danych programu SQL Server. Następnie można użyć tego parametru w oknie dialogowym bazy danych programu SQL Server.

Jeśli zmienisz lokalizację serwera, wystarczy zaktualizować parametr nazwy serwera, a zapytania zostaną zaktualizowane.

Uwaga

Aby dowiedzieć się więcej na temat tworzenia i używania parametrów, przejdź do tematu Używanie parametrów.

Tworzenie funkcji wielokrotnego użytku

Jeśli znajdziesz się w sytuacji, w której konieczne jest zastosowanie tego samego zestawu przekształceń do różnych zapytań lub wartości, utworzenie funkcji niestandardowej dodatku Power Query, która może być ponownie użyta tyle razy, ile potrzebujesz, może być korzystne. Funkcja niestandardowa dodatku Power Query to mapowanie z zestawu wartości wejściowych na pojedynczą wartość wyjściową i jest tworzone na podstawie natywnych funkcji i operatorów języka M.

Załóżmy na przykład, że masz wiele zapytań lub wartości, które wymagają tego samego zestawu przekształceń. Można utworzyć funkcję niestandardową, która później może zostać wywołana względem wybranej kwerendy lub wartości. Ta funkcja niestandardowa pozwala zaoszczędzić czas i pomóc w zarządzaniu zestawem przekształceń w centralnej lokalizacji, którą można modyfikować w dowolnym momencie.

Funkcje niestandardowe dodatku Power Query można tworzyć na podstawie istniejących zapytań i parametrów. Załóżmy na przykład, że zapytanie zawiera kilka kodów jako ciąg tekstowy i chcesz utworzyć funkcję, która zdekoduje te wartości.

Zacznij od parametru, który ma wartość, która służy jako przykład.

Z tego parametru utworzysz nowe zapytanie, w którym stosujesz potrzebne przekształcenia. W tym przypadku chcesz podzielić kod PTY-CM1090-LAX na wiele składników:

• Źródło = PTY
• Miejsce docelowe = LAX
• Linia lotnicza = CM
• FlightID = 1090

Następnie możesz przekształcić to zapytanie w funkcję, klikając prawym przyciskiem myszy zapytanie i wybierając polecenie Utwórz funkcję. Na koniec możesz wywołać funkcję niestandardową do dowolnych zapytań lub wartości, jak pokazano na poniższej ilustracji.

Po kilku kolejnych przekształceniach można zobaczyć, że osiągnięto żądane dane wyjściowe i wykorzystał logikę do takiego przekształcenia z funkcji niestandardowej.

Uwaga

Aby dowiedzieć się więcej na temat tworzenia i używania funkcji niestandardowych w dodatku Power Query, zobacz Custom Functions.