Korzystanie z funkcji niestandardowych

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.

Chociaż możesz ręcznie utworzyć własną funkcję niestandardową dodatku Power Query przy użyciu kodu zgodnie z opisem w temacie Opis funkcji power Query M, interfejs użytkownika dodatku Power Query oferuje funkcje umożliwiające przyspieszenie, uproszczenie i ulepszenie procesu tworzenia funkcji niestandardowej i zarządzania nią.

Ten artykuł koncentruje się na tym środowisku, udostępnianym tylko za pośrednictwem interfejsu użytkownika dodatku Power Query i sposobu, aby maksymalnie go wykorzystać.

Ważne

W tym artykule opisano sposób tworzenia funkcji niestandardowej za pomocą dodatku Power Query przy użyciu typowych przekształceń dostępnych w interfejsie użytkownika dodatku Power Query. Koncentruje się on na podstawowych pojęciach dotyczących tworzenia funkcji niestandardowych oraz linki do dodatkowych artykułów w dokumentacji dodatku Power Query, aby uzyskać więcej informacji na temat konkretnych przekształceń, do których odwołuje się ten artykuł.

Tworzenie funkcji niestandardowej na podstawie odwołania do tabeli

Uwaga

Poniższy przykład został utworzony przy użyciu środowiska pulpitu znajdującego się w programie Power BI Desktop i można go również obserwować przy użyciu środowiska Dodatku Power Query znajdującego się w programie Excel dla systemu Windows.

Możesz skorzystać z tego przykładu, pobierając przykładowe pliki używane w tym artykule z poniższego linku pobierania. Dla uproszczenia w tym artykule jest używany łącznik Folder. Aby dowiedzieć się więcej o łączniku folderów, przejdź do folderu. Celem tego przykładu jest utworzenie funkcji niestandardowej, która może zostać zastosowana do wszystkich plików w tym folderze przed połączeniem wszystkich danych ze wszystkich plików w jedną tabelę.

Zacznij od użycia środowiska łącznika folderu, aby przejść do folderu, w którym znajdują się pliki, i wybrać pozycję Przekształć dane lub Edytuj. Te kroki umożliwiają przejście do środowiska dodatku Power Query. Kliknij prawym przyciskiem myszy wybraną wartość binarną w polu Zawartość i wybierz opcję Dodaj jako nowe zapytanie . W tym przykładzie wybrano pierwszy plik z listy, który ma być plikiem z kwietnia 2019.csv.

Ta opcja skutecznie tworzy nowe zapytanie z krokiem nawigacji bezpośrednio do tego pliku jako plik binarny, a nazwa tego nowego zapytania to ścieżka pliku wybranego pliku. Zmień nazwę tego zapytania na Przykładowy plik.

Utwórz nowy parametr o nazwie Parametr pliku i typ pliku binarnego. Użyj zapytania Przykładowy plik jako wartości domyślnej i bieżącej wartości.

Uwaga

Zalecamy przeczytanie artykułu na temat parametrów , aby lepiej zrozumieć sposób tworzenia parametrów i zarządzania nimi w dodatku Power Query.

Funkcje niestandardowe można tworzyć przy użyciu dowolnego typu parametrów. Nie ma potrzeby, aby żadna funkcja niestandardowa miała plik binarny jako parametr.

Typ parametru binarnego jest wyświetlany tylko w menu rozwijanym Parametry Typ okna dialogowego, gdy masz zapytanie, które daje w wyniku dane binarne.

Istnieje możliwość utworzenia funkcji niestandardowej bez parametru. Jest to często spotykane w scenariuszach, w których dane wejściowe można wywnioskować ze środowiska, w którym jest wywoływana funkcja. Na przykład funkcja, która przyjmuje bieżącą datę i godzinę środowiska, i tworzy określony ciąg tekstowy z tych wartości.

Kliknij prawym przyciskiem myszy parametr pliku w okienku Zapytania . Wybierz opcję Odwołanie.

Zmień nazwę nowo utworzonego zapytania z parametru pliku (2) na Przekształć przykładowy plik.

Kliknij prawym przyciskiem myszy to nowe zapytanie przekształć przykładowy plik i wybierz opcję Utwórz funkcję .

Ta operacja skutecznie tworzy nową funkcję, która łączy się z zapytaniem przekształć przykładowy plik . Wszelkie zmiany wprowadzone w zapytaniu przekształć przykładowy plik są automatycznie replikowane do funkcji niestandardowej. Podczas tworzenia tej nowej funkcji użyj polecenia Przekształć plik jako nazwę funkcji.

Po utworzeniu funkcji zwróć uwagę, że zostanie utworzona nowa grupa o nazwie funkcji. Ta nowa grupa zawiera:

• Wszystkie parametry, do których odwołuje się zapytanie transform sample file .
• Zapytanie przekształć przykładowy plik , powszechnie znane jako przykładowe zapytanie.
• Nowo utworzona funkcja, w tym przypadku plik Transform.

Stosowanie przekształceń do przykładowego zapytania

Po utworzeniu nowej funkcji wybierz zapytanie o nazwie Przekształć przykładowy plik. To zapytanie jest teraz połączone z funkcją Przekształć plik , więc wszystkie zmiany wprowadzone w tym zapytaniu zostaną odzwierciedlone w funkcji. To połączenie jest nazywane pojęciem przykładowego zapytania połączonego z funkcją.

Pierwszą transformacją, która musi się zdarzyć w tym zapytaniu, jest taka, która interpretuje dane binarne. Możesz kliknąć prawym przyciskiem myszy plik binarny w okienku podglądu i wybrać opcję CSV , aby zinterpretować plik binarny jako plik CSV.

Format wszystkich plików CSV w folderze jest taki sam. Wszystkie mają nagłówek obejmujący pierwsze cztery pierwsze wiersze. Nagłówki kolumn znajdują się w wierszu 5, a dane zaczynają się od wiersza 6 w dół, jak pokazano na następnej ilustracji.

Następny zestaw kroków przekształcania, które należy zastosować do pliku Transform Sample, to:

1. Usuń cztery pierwsze wiersze — ta akcja usuwa wiersze, które są uważane za część sekcji nagłówka pliku.

   

   Uwaga

   Aby dowiedzieć się więcej na temat usuwania wierszy lub filtrowania tabeli według położenia wiersza, przejdź do pozycji Filtruj według pozycji wiersza.

2. Podwyższanie poziomu nagłówków — nagłówki końcowej tabeli znajdują się teraz w pierwszym wierszu tabeli. Można je podwyższyć poziom, jak pokazano na następnej ilustracji.

   

Po podwyższeniu poziomu nagłówków kolumn dodatek Power Query domyślnie automatycznie dodaje nowy krok Zmieniony typ , który automatycznie wykrywa typy danych dla każdej kolumny. Zapytanie przekształć przykładowy plik wygląda podobnie do następnego obrazu.

Uwaga

Aby dowiedzieć się więcej na temat promowania i obniżania nagłówków, zobacz Podwyższanie lub obniżanie nagłówków kolumn.

Uwaga

Funkcja przekształć plik opiera się na krokach wykonywanych w zapytaniu Przekształć przykładowy plik . Jeśli jednak spróbujesz ręcznie zmodyfikować kod funkcji Przekształć plik , zostanie wyświetlone ostrzeżenie, które odczytuje The definition of the function 'Transform file' is updated whenever query 'Transform Sample file' is updated. However, updates will stop if you directly modify function 'Transform file'.

Wywoływanie funkcji niestandardowej jako nowej kolumny

Po utworzeniu funkcji niestandardowej i włączeniu wszystkich kroków przekształcania możesz wrócić do oryginalnego zapytania, w którym znajduje się lista plików z folderu (pliki CSV w tym przykładzie). Na karcie Dodawanie kolumny na wstążce wybierz pozycję Wywołaj funkcję niestandardową z grupy Ogólne. W oknie Invoke Custom Function (Wywoływanie funkcji niestandardowej) wprowadź wartość Output Table (Tabela danych wyjściowych) jako nazwę nowej kolumny. Wybierz nazwę funkcji , Przekształć plik z listy rozwijanej Zapytanie funkcji. Po wybraniu funkcji z menu rozwijanego zostanie wyświetlony parametr funkcji i można wybrać kolumnę z tabeli do użycia jako argument tej funkcji. Wybierz kolumnę Zawartość jako wartość /argument, który ma zostać przekazany dla parametru pliku.

Po wybraniu przycisku OK zostanie utworzona nowa kolumna o nazwie Tabela wyjściowa . Ta kolumna zawiera wartości tabeli w komórkach, jak pokazano na następnej ilustracji. Dla uproszczenia usuń wszystkie kolumny z tej tabeli z wyjątkiem tabeli Name (Nazwa ) i Output Table (Tabela wyjściowa).

Uwaga

Aby dowiedzieć się więcej na temat wybierania lub usuwania kolumn z tabeli, przejdź do artykułu Wybieranie lub usuwanie kolumn.

Funkcja została zastosowana do każdego wiersza z tabeli przy użyciu wartości z kolumny Content jako argumentu funkcji. Teraz, gdy dane są przekształcane w kształt, którego szukasz, możesz rozwinąć kolumnę Tabela danych wyjściowych , wybierając ikonę Rozwiń. Nie używaj żadnego prefiksu dla rozszerzonych kolumn.

Możesz sprawdzić, czy masz dane ze wszystkich plików w folderze, sprawdzając wartości w kolumnie Nazwa lub Data . W tym przypadku można sprawdzić wartości z kolumny Date (Data ), ponieważ każdy plik zawiera tylko dane dla jednego miesiąca z danego roku. Jeśli widzisz więcej niż jedną, oznacza to, że dane z wielu plików zostały pomyślnie połączone z wieloma plikami w jedną tabelę.

Uwaga

To, co przeczytano do tej pory, jest zasadniczo tym samym procesem, który występuje podczas korzystania z funkcji Połącz pliki , ale ręcznie.

Zalecamy również zapoznanie się z artykułem Łączenie plików i Łączenie plików CSV, aby lepiej zrozumieć, jak działa środowisko łączenia plików w dodatku Power Query i rolę, jaką odgrywają funkcje niestandardowe.

Dodawanie nowego parametru do istniejącej funkcji niestandardowej

Wyobraź sobie, że istnieje nowe wymaganie dotyczące aktualnie utworzonego elementu. Nowe wymaganie wymaga, aby przed połączeniem plików przefiltrować dane wewnątrz nich, aby uzyskać tylko wiersze, w których kraj jest równy Panamie.

Aby to wymaganie się stało, utwórz nowy parametr o nazwie Market z typem danych tekstowych. W polu Bieżąca wartość wprowadź wartość Panama.

Za pomocą tego nowego parametru wybierz zapytanie Przekształć przykładowy plik i przefiltruj pole Country przy użyciu wartości z parametru Market .

Uwaga

Aby dowiedzieć się więcej na temat filtrowania kolumn według wartości, przejdź do tematu Filtruj wartości.

Zastosowanie tego nowego kroku do zapytania powoduje automatyczne zaktualizowanie funkcji pliku Transform, która teraz wymaga dwóch parametrów na podstawie dwóch parametrów używanych przez plik Transform Sample.

Jednak zapytanie dotyczące plików CSV zawiera obok niego znak ostrzegawczy. Teraz, gdy funkcja została zaktualizowana, wymaga dwóch parametrów. Dlatego krok, w którym wywołujesz funkcję, powoduje wyświetlenie wartości błędów, ponieważ tylko jeden z argumentów został przekazany do funkcji transform pliku podczas kroku Wywołana funkcja niestandardowa.

Aby naprawić błędy, kliknij dwukrotnie wywołaną funkcję niestandardową w zastosowanych krokach, aby otworzyć okno Wywołaj funkcję niestandardową. W parametrze Market ręcznie wprowadź wartość Panama.

Teraz możesz wrócić do rozwiniętej tabeli danych wyjściowych w zastosowanych krokach. Sprawdź zapytanie, aby sprawdzić, czy w końcowym zestawie wyników zapytania Pliki CSV są wyświetlane tylko wiersze, w których kraj jest równy Panamie.

Tworzenie funkcji niestandardowej na podstawie logiki wielokrotnego użytku

Jeśli masz wiele zapytań lub wartości, które wymagają tego samego zestawu przekształceń, możesz utworzyć funkcję niestandardową, która działa jako element logiki wielokrotnego użytku. Później tę funkcję niestandardową można wywołać na wybrane zapytania lub wartości. Ta funkcja niestandardowa może zaoszczędzić czas i pomóc w zarządzaniu zestawem przekształceń w centralnej lokalizacji, którą można modyfikować w dowolnym momencie.

Załóżmy na przykład, że zapytanie zawierające kilka kodów jako ciąg tekstowy i chcesz utworzyć funkcję, która dekoduje te wartości, jak w poniższej przykładowej tabeli:

code
PTY-CM1090-LAX
LAX-CM701-PTY
PTY-CM4441-MIA
MIA-UA1257-LAX
LAX-XY2842-MIA

Zacznij od parametru, który ma wartość, która służy jako przykład. W tym przypadku jest to wartość PTY-CM1090-LAX.

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

Poniższy kod języka M pokazuje, że zestaw przekształceń.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Uwaga

Aby dowiedzieć się więcej na temat języka formuł Power Query M, przejdź do języka formuł Power Query M.

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 dowolnego zapytania lub wartości, jak pokazano na następnej ilustracji.

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