Najlepsze praktyki dotyczące pracy z dodatkiem Power Query

Ten artykuł zawiera kilka porad i wskazówek, aby jak najlepiej wykorzystać doświadczenie w przygotowaniu danych w dodatku Power Query.

Wybieranie odpowiedniego łącznika

Power Query oferuje ogromną liczbę łączy 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 do zadania zapewnia najlepsze doświadczenie i wydajność. Na przykład użycie łącznika SQL Server zamiast łącznika ODBC podczas nawiązywania połączenia z bazą danych SQL Server nie tylko zapewnia lepsze doświadczenie Pobierania Danych, ale łącznik SQL Server oferuje również funkcje, które mogą poprawić doświadczenie 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 konektor danych działa w standardowy sposób, jak wyjaśniono w sekcji 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 .

Uwaga / Notatka

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

Filtruj wcześnie

Zawsze zalecamy filtrowanie danych na wczesnych etapach zapytania lub jak najszybsze. Niektóre łączniki korzystają z filtrów dzięki składaniu zapytań, tak jak opisano 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. Takie filtrowanie pozwala 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.

Uwaga / Notatka

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

Czy kosztowne operacje trwają długo?

Niektóre operacje wymagają odczytania pełnego źródła danych w celu zwrócenia jakichkolwiek wyników, a tym samym jest powolne wyświetlanie podglądu w edytorze Power Query. 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 przepływają, a wyniki są zwracane po drodze. W edytorze 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, a następnie wykonaj pozostałe, bardziej kosztowne operacje. Wykonywanie operacji w tej kolejności pomaga 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 edytorze Power Query działa wolno, najpierw należy wykonać operację "Zachowaj pierwsze wiersze" i ograniczyć liczbę 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 Kolumna daty i godziny w menu Dodaj kolumnę można używać. Jeśli jednak kolumna nie ma ustawionego typu danych, te opcje są 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 są 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 są wprowadzane ze schematu tabeli znajdującego się 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 / Notatka

Aby dowiedzieć się więcej o znaczeniu typów danych i sposobie ich pracy, przejdź do tematu 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 dla każdej 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 ułatwia przygotowanie danych.

Uwaga / Notatka

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 / Notatka

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 oknem dialogowym, aby nazwać nowe zapytanie. Ten krok skutecznie dzieli zapytanie na dwa zapytania. Jedno zapytanie zawiera wszystkie zapytania przed scaleniem. Inne zapytanie zawiera początkowy krok, który odnosi się do nowego zapytania, oraz pozostałe kroki z oryginalnego zapytania, począwszy od kroku Tabeli Scalanie z Cenami w dół.

Możesz również użyć odwołań do zapytań, jak uważasz za stosowne. Ale dobrym pomysłem jest utrzymanie zapytań na poziomie, który nie wydaje się zniechęcający na pierwszy rzut oka przy tak wielu krokach.

Uwaga / Notatka

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

Tworzenie grup

Doskonałym sposobem na zorganizowanie pracy jest użycie 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 zajdzie taka potrzeba. Przenoszenie zapytań między grupami jest równie proste jak przeciągnięcie i upuszczenie.

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

Uwaga / Notatka

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ść

Upewnienie się, że utworzone zapytanie 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.

Najlepszą praktyką jest zdefiniowanie zakresu zapytania, co powinno robić i co należy uwzględnić pod względem struktury, układu, nazw kolumn, typów danych oraz każdego innego elementu, który uważasz za istotny dla zakresu.

Oto kilka przykładów przekształceń, które mogą pomóc w uczynieniu zapytania odpornym 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 / Notatka

  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 / Notatka

  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 odzestawić tylko podzbiór kolumn, możesz użyć funkcji odzestawiania tylko wybranych kolumn.

  Uwaga / Notatka

  Aby dowiedzieć się więcej o opcjach anulowania przestawienia kolumn, przejdź do sekcji Anulowanie przestawiania kolumn.

• 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 / Notatka

  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 odwoływać się do parametrów jako argumentów 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 / Notatka

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

Możesz znaleźć się w sytuacji, w której trzeba zastosować ten sam zestaw przekształceń do różnych zapytań lub wartości. W takim przypadku 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 ułatwić zarządzanie 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 dekoduje te wartości.

Zacznij od utworzenia parametru z wartością, 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
• docelowy = 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ą w dowolnym zapytaniu lub wartości.

Po kilku kolejnych przekształceniach widać, że osiągnięto żądane dane wyjściowe i zastosowano logikę dla takiej transformacji z funkcji niestandardowej.

Uwaga / Notatka

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