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.

Możesz ręcznie utworzyć własną funkcję niestandardową dodatku Power Query przy użyciu języka formuł Power Query Mlub interfejs użytkownika dodatku Power Query oferuje funkcje umożliwiające przyspieszenie, uproszczenie i ulepszenie procesu tworzenia funkcji niestandardowej i zarządzania nią.

Najpierw omówimy podstawowe kroki utworzenia funkcji niestandardowej z kodem w interfejsie użytkownika, a następnie skoncentrujemy się na użyciu interfejsu, aby przekształcić złożone akcje na funkcję wielokrotnego użytku.

Ważny

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 i linkach do innych 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 kodu w interfejsie użytkownika

Notatka

Poniższe kroki można wykonać w programie Power BI Desktop lub za pomocą środowiska Power Query znajdującego się w programie Excel dla systemu Windows.

1. Użyj funkcji łącznika, aby połączyć się z danymi w miejscu ich przechowywania. Po wybraniu danych wybierz przycisk Przekształć dane lub Edytuj. Spowoduje to przejście do środowiska Power Query.
2. Kliknij prawym przyciskiem myszy puste miejsce w okienku Zapytania po lewej stronie.
3. Wybierz Puste Zapytanie.
4. W nowym pustym oknie zapytania wybierz menu Narzędzia główne, a następnie Edytor zaawansowany.
5. Zastąp szablon funkcją niestandardową. Na przykład:

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Wybierz pozycję Gotowe.

Aby uzyskać więcej informacji na temat opracowywania funkcji niestandardowych przy użyciu języka formuł Power Query M, zobacz ten artykuł: Understanding Power Query M Functions. W poniższych sekcjach przedstawiono samouczki opisujące sposób używania interfejsu użytkownika dodatku Power Query do tworzenia funkcji niestandardowych bez pisania kodu oraz instrukcji dotyczących sposobu wywoływania funkcji niestandardowej w zapytaniu.

Samouczek dotyczący tworzenia funkcji niestandardowej na podstawie dokumentacji tabeli

Notatka

Poniższy przykład został utworzony przy użyciu wersji desktopowej programu Power BI Desktop i można go również zrealizować przy użyciu Power Query dostępnego w Excelu 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 Folder. 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 narzędzia łącznika folderu, aby przejść do folderu, w którym znajdują się pliki, i wybierz pozycję Przekształć dane lub Edytuj. Kroki te prowadzą do doświadczenia z Power Query. Kliknij prawym przyciskiem myszy wybraną wartość Binary w polu Zawartość i wybierz opcję Dodaj jako nowe zapytanie. W tym przykładzie wybrano pierwszy plik z listy, który to plik to Kwiecień 2019.csv.

Ta opcja skutecznie tworzy nowe zapytanie z krokiem nawigacji bezpośrednio do tego pliku w postaci binarnej, a nazwą tego nowego zapytania jest ścieżka do wybranego pliku. Zmień nazwę tego zapytania na Przykładowy plik.

Utwórz nowy parametr o nazwie File Parameter i typ Binary. Użyj zapytania przykładowego pliku jako wartości domyślnej i bieżącej wartości.

Notatka

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 oknie dialogowym Parametry w menu rozwijanym Typ, gdy masz zapytanie, które ocenia się jako 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 File Parameter w okienku Queries. Wybierz opcję Dokumentacja.

Zmień nazwę nowo utworzonego zapytania z File Parameter (2) na Transform Sample file.

Kliknij prawym przyciskiem myszy ten nowy plik Przekształć przykładowy zapytanie i wybierz opcję Utwórz funkcję.

Ta operacja skutecznie tworzy nową funkcję, która łączy się z zapytaniem Transform Sample file. Wszelkie zmiany wprowadzone w pliku Transform Sample zapytania są automatycznie replikowane do funkcji niestandardowej. Podczas tworzenia tej nowej funkcji użyj pliku Transform jako nazwy 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 pliku Transform Sample.
• Twoje zapytanie do pliku próbkowego Transform Sample, powszechnie znane jako zapytanie próbki .
• Nowo utworzona funkcja, w tym przypadku Przekształć plik .

Stosowanie przekształceń do przykładowego zapytania

Po utworzeniu nowej funkcji wybierz zapytanie o nazwie Transform Sample file. To zapytanie jest teraz powiązane z funkcją pliku transformacji , więc wszystkie zmiany wprowadzone w tym zapytaniu będą 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ć CSV opcję interpretowania pliku binarnego jako pliku CSV.

Format wszystkich plików CSV w folderze jest taki sam. Wszystkie mają nagłówek obejmujący 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 są następujące:

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

   

   Notatka

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

2. Promuj nagłówki— nagłówki końcowej tabeli znajdują się teraz w pierwszym wierszu tabeli. Można je wypromować, jak pokazano na następnym obrazku.

   

Po podwyższeniu poziomu nagłówków kolumn, dodatek Power Query domyślnie dodaje nowy krok Zmieniono typ, który wykrywa typy danych dla każdej kolumny. Zapytanie przykładowego pliku Transform wygląda jak na następnym obrazie.

Notatka

Aby dowiedzieć się więcej o promowaniu lub degradowaniu poziomów nagłówków kolumn, przejdź do Promowanie lub degradowanie poziomów nagłówków kolumn.

Ostrożność

Funkcja Przekształć plik opiera się na krokach wykonywanych w zapytaniu Przekształć przykładowy plik. Jeśli jednak spróbujesz ręcznie zmodyfikować kod funkcji Transform pliku, zostanie wyświetlone ostrzeżenie, które brzmi 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łaj funkcję niestandardową jako nową kolumnę

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 Dodaj kolumnę na wstążce wybierz pozycję Wywołaj funkcję niestandardową z grupy Ogólne. W oknie Wywołaj funkcję niestandardową wprowadź Tabela wyjściowa jako Nowa nazwa kolumny. Wybierz nazwę swojej funkcji, Przekształć plik, z listy rozwijanej zapytania 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ę Content jako wartość / argument, która ma zostać przekazana dla parametru pliku .

Po wybraniu OKzostanie utworzona nowa kolumna o nazwie Output Table. W komórkach tej kolumny znajdują się wartości tabeli , jak pokazano na kolejnym obrazku. Dla uproszczenia usuń wszystkie kolumny z tej tabeli z wyjątkiem Name i Output Table.

Notatka

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

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 szukany kształt, możesz rozwinąć kolumnę Tabeli wyjściowej, 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 Name lub Date. W tym przypadku można sprawdzić wartości z kolumny Date, 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 w jedną tabelę.

Notatka

To, co do tej pory przeczytałeś/aś, jest zasadniczo tym samym procesem, który występuje podczas Łączenie plików doświadczenia, ale wykonywany ręcznie.

Zalecamy również przeczytanie artykułu na temat Łączenie plików — omówienie 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 tego, co obecnie stworzyłeś. Nowe wymaganie wymaga, aby przed połączeniem plików przefiltrować dane wewnątrz nich, aby uzyskać tylko wiersze, w których Country jest równe Panama.

Aby spełnić to wymaganie, utwórz nowy parametr o nazwie Market z tekstowym typem danych. Dla Wartości Bieżącej , wprowadź wartośćPanama.

Przy użyciu tego nowego parametru wybierz zapytanie pliku Transform Sample i przefiltruj pole Country przy użyciu wartości z parametru Market.

Notatka

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

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

Ale obok plików CSV zapytanie ma znak ostrzegawczy. Teraz, gdy funkcja została zaktualizowana, wymaga dwóch parametrów. Dlatego krok, w którym wywołujesz funkcję, prowadzi do błędów, ponieważ tylko jeden z argumentów został przekazany do funkcji Transform podczas kroku Wywołanej Funkcji Niestandardowej.

Aby naprawić błędy, kliknij dwukrotnie Wywołana funkcja niestandardowa w Zastosowane kroki, aby otworzyć okno wywołania funkcji niestandardowej. W parametrze rynek wprowadź ręcznie wartość Panama.

Teraz możesz wrócić do tabeli wyjściowej rozwiniętej w sekcji Zastosowane kroki. Sprawdź zapytanie, aby upewnić się, że tylko wiersze, w których Country jest równe Panama, są wyświetlane w końcowym zestawie wyników zapytania z plików CSV.

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:

kod
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:

• Origin = PTY
• docelowy = LAX
• Linia lotnicza = CM
• FlightID = 1090

Poniższy kod w języku M demonstruje 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"

Notatka

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 Utwórz funkcję. Na koniec możesz wywołać funkcję niestandardową w dowolnym zapytaniu lub dla dowolnej 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.