Verwenden von benutzerdefinierten FunktionenUsing custom functions

Wenn Sie sich in einer Situation befinden, in der Sie denselben Satz von Transformationen auf verschiedene Abfragen oder Werte anwenden müssen, können Sie eine Power Query benutzerdefinierte Funktion erstellen, die so oft wie nötig wieder verwendet werden kann.If you find yourself in a situation where you need to apply the same set of transformations to different queries or values, creating a Power Query custom function that can be reused as many times as you need could be beneficial. Eine Power Query benutzerdefinierte Funktion ist eine Zuordnung von einem Satz von Eingabe Werten zu einem einzelnen Ausgabewert und wird aus nativen M-Funktionen und-Operatoren erstellt.A Power Query custom function is a mapping from a set of input values to a single output value, and is created from native M functions and operators.

Sie können zwar manuell eine eigene Power Query benutzerdefinierte Funktion mit Code erstellen, wie in Grundlegendes zu Power Query M-Funktionengezeigt, aber die Power Query-Benutzeroberfläche bietet Features, mit denen Sie den Prozess der Erstellung und Verwaltung einer benutzerdefinierten Funktion beschleunigen, vereinfachen und verbessern können.While you can manually create your own Power Query custom function using code as shown in Understanding Power Query M functions, the Power Query user interface offers you features to speed up, simplify, and enhance the process of creating and managing a custom function. Dieser Artikel konzentriert sich auf diese Oberfläche, die nur über die Power Query Benutzeroberfläche bereitgestellt wird, und wie Sie diese optimal nutzen können.This article focuses on this experience provided only through the Power Query user interface and how to get the most out of it.

Erstellen einer benutzerdefinierten Funktion aus einem Tabellen VerweisCreate a custom function from a table reference

Sie können dieses Beispiel verwenden, indem Sie die Beispieldateien, die in diesem Artikel verwendet werden, von folgendem Download Linkherunterladen.You can follow along with this example by downloading the sample files used in this article from the following download link. Der Einfachheit halber wird in diesem Artikel der Ordner-Connector verwendet.For simplicity, this article will be using the Folder connector. Weitere Informationen zum Ordner-Connector finden Sie unter Ordner.To learn more about the Folder connector, see Folder. Das Ziel dieses Beispiels besteht darin, eine benutzerdefinierte Funktion zu erstellen, die auf alle Dateien in diesem Ordner angewendet werden kann, bevor alle Daten aus allen Dateien in einer einzigen Tabelle kombiniert werden.The goal of this example is to create a custom function that can be applied to all the files in that folder before combining all of the data from all files into a single table.

Klicken Sie nach dem Herstellen einer Verbindung mit dem Ordner, in dem sich die Dateien befinden, mit der rechten Maustaste auf den binären Wert Ihrer Wahl aus dem Feld Inhalt , und wählen Sie die Option als neue Abfrage hinzufügen aus.After connecting to the folder where your files are located, right-click on the Binary value of your choice from the Content field and select the Add as New Query option. In diesem Beispiel sehen Sie, dass die Auswahl für die erste Datei in der Liste getroffen wurde, die die Datei April 2019.csvist.For this example, you'll see that the selection was made for the first file from the list, which happens to be the file April 2019.csv.

Auswählen einer Datei als Beispieldatei

Mit dieser Option wird eine neue Abfrage mit einem Navigations Schritt direkt zu dieser Datei als Binärdatei erstellt, und der Name dieser neuen Abfrage ist der Dateipfad der ausgewählten Datei.This option will effectively create a new query with a navigation step directly to that file as a Binary, and the name of this new query will be the file path of the selected file. Benennen Sie diese Abfrage in eine Beispieldateium.Rename this query to be Sample File.

Beispiel für eine Datei Abfrage

Erstellen Sie einen neuen Parameter mit dem Parameter"Name File".Create a new parameter with the name File Parameter. Verwenden Sie die Beispieldatei Abfrage als aktuellen Wert, wie in der folgenden Abbildung dargestellt.Use the Sample File query as the Current Value, as shown in the following image.

File-Parameter

Hinweis

Es wird empfohlen, dass Sie den Artikel zu Parametern lesen, um besser zu verstehen, wie Parameter in Power Query erstellt und verwaltet werden.We recommend that you read the article on Parameters to better understand how to create and manage parameters in Power Query.

Benutzerdefinierte Funktionen können mit jedem Parametertyp erstellt werden.Custom functions can be created using any parameters type. Es ist nicht erforderlich, dass eine benutzerdefinierte Funktion eine Binärdatei als Parameter hat.There's no requirement for any custom function to have a binary as a parameter.

Es ist möglich, eine benutzerdefinierte Funktion ohne Parameter zu erstellen.It's possible to create a custom function without a parameter. Dies tritt häufig in Szenarien auf, in denen eine Eingabe aus der Umgebung abgeleitet werden kann, in der die Funktion aufgerufen wird.This is commonly seen in scenarios where an input can be inferred from the environment where the function is being invoked. Dies ist z. b. eine Funktion, die das aktuelle Datum und die aktuelle Uhrzeit der Umgebung annimmt und eine bestimmte Text Zeichenfolge aus diesen Werten erstellt.For example, a function that takes the environment's current date and time, and creates a specific text string from those values.

Klicken Sie im Bereich Abfragen mit der rechten Maustaste auf file-Parameter .Right-click File Parameter from the Queries pane. Wählen Sie die Option Verweis aus.Select the Reference option.

Verweis auf den file-Parameter

Benennen Sie die neu erstellte Abfrage aus dem Datei Parameter (2) um, um die Beispieldatei umzuwandeln.Rename the newly created query from File Parameter (2) to Transform Sample file.

Umbenannte Beispieldatei für die Abfrage Transformation

Klicken Sie mit der rechten Maustaste auf diese neue Transformations Beispieldatei Abfrage, und wählen Sie die Option CREATE FUNCTION aus.Right-click this new Transform Sample file query and select the Create Function option.

Erstellen einer Funktion aus der Transformations Beispieldatei

Mit diesem Vorgang wird eine neue Funktion erstellt, die mit der Transformation Sample File Query verknüpft wird.This operation will effectively create a new function that will be linked with the Transform Sample file query. Alle Änderungen, die Sie an der Transformations Beispieldatei -Abfrage vornehmen, werden automatisch in Ihrer benutzerdefinierten Funktion repliziert.Any changes that you make to the Transform Sample file query will be automatically replicated to your custom function. Verwenden Sie beim Erstellen dieser neuen Funktion die Transformations Datei als Funktionsname.During the creation of this new function, use Transform file as the Function name.

Erstellen eines Funktions Fensters für die Transformations Datei

Nachdem Sie die Funktion erstellt haben, werden Sie feststellen, dass eine neue Gruppe mit dem Namen ihrer Funktion erstellt wird.After creating the function, you'll notice that a new group will be created for you with the name of your function. Diese neue Gruppe enthält Folgendes:This new group will contain:

  • Alle Parameter, auf die in der Transformations-Beispieldatei Abfrage verwiesen wurde.All parameters that were referenced in your Transform Sample file query.
  • Die Transformations Beispieldatei -Abfrage, die in der Regel als Beispiel Abfragebezeichnet wird.Your Transform Sample file query, commonly known as the sample query.
  • Die neu erstellte Funktion, in diesem Fall die Transformations Datei.Your newly created function, in this case Transform file.

Funktionsgruppe

Anwenden von Transformationen auf eine Beispiel AbfrageApplying transformations to a sample query

Wählen Sie bei der Erstellung der neuen Funktion die Abfrage mit der Beispieldatei Name Transformationaus.With your new function created, select the query with the name Transform Sample file. Diese Abfrage ist jetzt mit der Transform File -Funktion verknüpft, sodass alle Änderungen an dieser Abfrage in der-Funktion wiedergegeben werden.This query is now linked with the Transform file function, so any changes made to this query will be reflected in the function. Dies wird als Konzept einer Beispiel Abfrage bezeichnet, die mit einer Funktion verknüpft ist.This is what is known as the concept of a sample query linked to a function.

Die erste Transformation, die mit dieser Abfrage ausgeführt werden muss, ist eine, die die Binärdatei interpretiert.The first transformation that needs to happen to this query is one that will interpret the binary. Sie können im Vorschaubereich mit der rechten Maustaste auf die Binärdatei klicken und die CSV -Option auswählen, um die Binärdatei als CSV-Datei zu interpretieren.You can right-click the binary from the preview pane and select the CSV option to interpret the binary as a CSV file.

Binärdatei als CSV-Datei interpretieren

Das Format aller CSV-Dateien im Ordner ist identisch.The format of all the CSV files in the folder is the same. Sie verfügen alle über einen Header, der die ersten vier Zeilen umfasst.They all have a header that spans the first top four rows. Die Spaltenüberschriften befinden sich in Zeile 5, und die Daten beginnen in Zeile 6 nach unten, wie in der folgenden Abbildung dargestellt.The column headers are located in row five and the data starts from row six downwards, as shown in the next image.

CSV-Beispiel Daten

Die nächste Reihe von Transformationsschritten, die auf die Transformations Beispieldatei angewendet werden müssen, lauten:The next set of transformation steps that need to be applied to the Transform Sample file are:

  1. Entfernen der ersten vier Zeilen — Mit dieser Aktion werden die Zeilen, die als Teil des Header Abschnitts der Datei angesehen werden, beseitigt.Remove the top four rows—This action will get rid of the rows that are considered part of the header section of the file.

    Entfernen der obersten Zeilen aus der Transformations Beispieldatei

    Hinweis

    Weitere Informationen zum Entfernen von Zeilen oder Filtern einer Tabelle nach Zeilen Position finden Sie unter Filtern nach Zeilen Position.To learn more about how to remove rows or filter a table by row position, see Filter by row position.

  2. Header — höher stufen Die Header für die endgültige Tabelle befinden sich jetzt in der ersten Zeile der Tabelle.Promote headers—The headers for your final table are now in the first row of the table. Sie können Sie herauf Stufen, wie in der nächsten Abbildung gezeigt.You can promote them as shown in the next image.

    Erste Zeile als Überschriften verwenden

Nach dem herauf Stufen der Spaltenüberschriften, die automatisch die Datentypen für die einzelnen Spalten erkennen, wird Power Query standardmäßig automatisch ein neuer geänderter Typ hinzugefügt.Power Query by default will automatically add a new Changed Type step after promoting your column headers that will automatically detect the data types for each column. Die Transformations Beispieldatei -Abfrage sieht wie das nächste Bild aus.Your Transform Sample file query will look like the next image.

Hinweis

Weitere Informationen zum herauf Stufen und Herabstufen von Headern finden Sie unter Promote or tiefer stufen Column Headers.To learn more about how to promote and demote headers, see Promote or demote column headers.

Abschließende Transformations Beispiel Abfrage

Achtung

Die Transformations Datei Funktion basiert auf den Schritten, die in der Transformations Beispieldatei -Abfrage ausgeführt werden.Your Transform file function relies on the steps performed in the Transform Sample file query. Wenn Sie jedoch versuchen, den Code für die Transformations Datei Funktion manuell zu ändern, wird eine Warnung mit einer Warnung angezeigt. 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'.However, if you try to manually modify the code for the Transform file function, you'll be greeted with a warning that reads 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'.

Aufrufen einer benutzerdefinierten Funktion als neue SpalteInvoke a custom function as a new column

Wenn die benutzerdefinierte Funktion nun erstellt wurde und alle Transformationsschritte integriert sind, können Sie zur ursprünglichen Abfrage zurückkehren, in der Sie über die Liste der Dateien aus dem Ordner verfügen.With the custom function now created and all the transformation steps incorporated, you can go back to the original query where you have the list of files from the folder. Wählen Sie im Menüband auf der Registerkarte Spalte hinzufügen die Option benutzerdefinierte Funktion aus der Gruppe Allgemein aufrufen aus.Inside the Add Column tab in the ribbon, select Invoke Custom Function from the General group. Geben Sie im Fenster benutzerdefinierte Funktion aufrufen die Ausgabe Tabelle als neuen Spaltennamenein.Inside the Invoke Custom Function window, enter Output Table as the New column name. Wählen Sie in der Dropdown Liste Funktions Abfrage den Namen ihrer Funktion, Transformations Dateiaus.Select the name of your function, Transform file, from the Function query dropdown. Nachdem Sie die Funktion im Dropdown Menü ausgewählt haben, wird der Parameter für die Funktion angezeigt, und Sie können auswählen, welche Spalte aus der Tabelle als Argument für diese Funktion verwendet werden soll.After selecting the function from the dropdown menu, the parameter for the function will be displayed and you can select which column from the table to use as the argument for this function. Wählen Sie die Spalte " Content " als Wert/Argument aus, das für den file-Parameterübergeben werden soll.Select the Content column as the value / argument to be passed for the File Parameter.

Schaltfläche "benutzerdefinierte Funktion aufrufen" im Menü "Spalte

Nachdem Sie auf OKgeklickt haben, wird eine neue Spalte mit der Name Output-Tabelle erstellt.After you select OK, a new column with the name Output Table will be created. Diese Spalte enthält Tabellen Werte in ihren Zellen, wie in der folgenden Abbildung dargestellt.This column has Table values in its cells, as shown in the next image. Entfernen Sie aus Gründen der Einfachheit alle Spalten aus dieser Tabelle mit Ausnahme von Name und Ausgabe Tabelle.For simplicity, remove all columns from this table except Name and Output Table.

Benutzerdefinierte Funktion aufgerufen

Hinweis

Weitere Informationen zum auswählen oder Entfernen von Spalten aus einer Tabelle finden Sie unter auswählen oder Entfernen von Spalten.To learn more about how to choose or remove columns from a table, see Choose or remove columns.

Die Funktion wurde auf jede einzelne Zeile aus der Tabelle angewendet, wobei die Werte aus der Spalte " Content " als Argument für die Funktion verwendet werden.Your function was applied to every single row from the table using the values from the Content column as the argument for your function. Nachdem Sie die Daten in die gesuchte Form transformiert haben, können Sie die Ausgabe Tabellen Spalte wie in der folgenden Abbildung dargestellt erweitern, ohne ein Präfix für die erweiterten Spalten zu verwenden.Now that the data has been transformed into the shape that you're looking for, you can expand the Output Table column, as shown in the image below, without using any prefix for the expanded columns.

Ausgabe Tabellenspalte erweitern

Sie können überprüfen, ob Sie über alle Dateien im Ordner verfügen, indem Sie die Werte in der Spalte " Name " oder " Date " überprüfen.You can verify that you have data from all files in the folder by checking the values in the Name or Date column. In diesem Fall können Sie die Werte in der Spalte Date überprüfen, da jede Datei nur Daten für einen einzelnen Monat eines bestimmten Jahres enthält.For this case, you can check the values from the Date column, as each file only contains data for a single month from a given year. Wenn Sie mehr als eins sehen, bedeutet dies, dass Sie Daten aus mehreren Dateien erfolgreich in einer einzigen Tabelle zusammengefasst haben.If you see more than one, it means that you've successfully combined data from multiple files into a single table.

Es wird überprüft, ob die letzte Tabelle Daten aus allen Dateien enthält.

Hinweis

Was Sie bisher gelesen haben, ist im Grunde der gleiche Prozess, der auch während der Kombination von Dateien durchgeführt wird, aber manuell ausgeführt wird.What you've read so far is fundamentally the same process that happens during the Combine files experience, but done manually.

Es wird empfohlen, dass Sie auch den Artikel Übersicht über das Kombinieren von Dateien und Kombinieren von CSV-Dateien lesen, um zu verstehen, wie die Kombination von Dateien in Power Query und die Rolle, die benutzerdefinierte Funktionen spielenWe recommend that you also read the article on Combine files overview and Combine CSV files to further understand how the combine files experience works in Power Query and the role that custom functions play.

Neuen Parameter zu vorhandener benutzerdefinierter Funktion hinzufügenAdd new parameter to existing custom function

Stellen Sie sich vor, dass eine neue Anforderung vorhanden ist, die Sie erstellt haben.Imagine that there's a new requirement on top of what you've built. Die neue Anforderung erfordert, dass Sie vor dem Kombinieren der Dateien die darin enthaltenen Daten filtern, um nur die Zeilen zu erhalten, in denen das Land zu Panamagehört.The new requirement requires that before you combine the files, you filter the data inside them to only get the rows where the Country is equals to Panama.

Um diese Anforderung zu erfüllen, erstellen Sie einen neuen Parameter namens Market mit dem Text-Datentyp.To make this requirement happen, create a new parameter called Market with the text data type. Geben Sie für den aktuellen Wertden Wert Panamaein.For the Current Value, enter the value Panama.

Neuer Parameter

Wählen Sie mit diesem neuen Parameter die Abfrage Beispieldatei transformieren aus, und Filtern Sie das Feld Country mit dem Wert aus dem Market -Parameter.With this new parameter, select the Transform Sample file query and filter the Country field using the value from the Market parameter.

Länder Spalte mithilfe des neuen Market-Parameters Filtern

Hinweis

Weitere Informationen zum Filtern von Spalten nach Werten finden Sie unter Filtern von Werten.To learn more about how to filter columns by values, see Filter values.

Wenn Sie diesen neuen Schritt auf die Abfrage anwenden, wird die Transformations Datei Funktion automatisch aktualisiert, die nun zwei Parameter erfordert, die auf den beiden Parametern basieren, die von der Transformations Beispieldatei verwendet werden.Applying this new step to your query will automatically update the Transform file function, which will now require two parameters based on the two parameters that your Transform Sample file uses.

Die Funktion wurde mit zwei Parametern aktualisiert.

Die CSV-Datei Abfrage enthält jedoch ein Warnsignal daneben.But the CSV files query has a warning sign next to it. Nachdem Sie nun die Funktion aktualisiert haben, sind zwei Parameter erforderlich.Now that your function has been updated, it requires two parameters. Der Schritt, in dem Sie die Funktion aufrufen, führt daher zu Fehler Werten, da während des aufgerufenen benutzerdefinierten Funktions Schritts nur eines der Argumente an die Transformations Datei Funktion übermittelt wurde.So the step where you invoke the function results in error values, since only one of the arguments was passed to the Transform file function during the Invoked Custom Function step.

Fehler nach Funktions Aktualisierung

Um die Fehler zu beheben, doppelklicken Sie in den angewendeten Schritten auf aufgerufene benutzerdefinierte Funktion , um das Fenster benutzerdefinierte Funktion aufrufen zu öffnen.To fix the errors, double-click Invoked Custom Function in the Applied Steps to open the Invoke Custom Function window. Geben Sie im Market -Parameter den Wert " Panama" manuell ein.In the Market parameter, manually enter the value Panama.

Aktualisieren von aufgerufenen benutzerdefinierten Funktions Argumenten

Sie können jetzt Ihre Abfrage überprüfen, um zu überprüfen, ob nur Zeilen, bei denen das Land gleich " Panama " ist, im endgültigen Resultset der CSV-Datei Abfrage angezeigt werden.You can now check your query to validate that only rows where Country is equal to Panama show up in the final result set of the CSV Files query.

Endgültige Ausgabe Tabelle nach aktualisierten Argumenten

Erstellen einer benutzerdefinierten Funktion aus einer wiederverwendbaren LogikCreate a custom function from a reusable piece of logic

Wenn Sie über mehrere Abfragen oder Werte verfügen, die denselben Satz von Transformationen erfordern, können Sie eine benutzerdefinierte Funktion erstellen, die als wiederverwendbare Logik fungiert.If you have multiple queries or values that require the same set of transformations, you could create a custom function that acts as a reusable piece of logic. Später kann diese benutzerdefinierte Funktion für die Abfragen oder Werte Ihrer Wahl aufgerufen werden.Later, this custom function can be invoked against the queries or values of your choice. Diese benutzerdefinierte Funktion spart Ihnen Zeit und hilft Ihnen bei der Verwaltung ihrer Transformationen an einem zentralen Ort, den Sie jederzeit ändern können.This custom function could save you time and help you in managing your set of transformations in a central location, which you can modify at any moment.

Stellen Sie sich beispielsweise eine Abfrage mit mehreren Codes als Text Zeichenfolge vor, und Sie möchten eine Funktion erstellen, die diese Werte decodiert.For example, imagine a query that has several codes as a text string and you want to create a function that will decode those values.

Liste der Codes

Sie beginnen mit einem Parameter, der über einen Wert verfügt, der als Beispiel fungiert.You start by having a parameter that has a value that serves as an example. In diesem Fall ist es der Wert Pty-CM1090-Lax.For this case, it will be the value PTY-CM1090-LAX.

Codewert für Beispiel Parameter

Mit diesem Parameter erstellen Sie eine neue Abfrage, bei der Sie die benötigten Transformationen anwenden.From that parameter, you create a new query where you apply the transformations that you need. In diesem Fall möchten Sie den Code Pty-CM1090-Lax in mehrere Komponenten aufteilen:For this case, you want to split the code PTY-CM1090-LAX into multiple components:

  • Ursprung = PtyOrigin = PTY
  • Ziel = LaxDestination = LAX
  • Fluggesellschaft = cmAirline = CM
  • Flightid = 1090FlightID = 1090

Beispiel für eine Transformations Abfrage

Der M-Code für diese Gruppe von Transformationen ist unten dargestellt.The M code for that set of transformations is shown below.

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"

Hinweis

Weitere Informationen zur Power Query m-Formelsprache finden Sie unter Power Query M-FormelspracheTo learn more about the Power Query M formula language, see Power Query M formula language

Anschließend können Sie diese Abfrage in eine Funktion umwandeln, indem Sie mit der rechten Maustaste auf die Abfrage klicken und CREATE FUNCTIONauswählen.You can then transform that query into a function by doing a right-click on the query and selecting Create Function. Zum Schluss können Sie die benutzerdefinierte Funktion in jeder Ihrer Abfragen oder Werte aufrufen, wie in der folgenden Abbildung dargestellt.Finally, you can invoke your custom function into any of your queries or values, as shown in the next image.

Aufrufen einer benutzerdefinierten Funktion

Nach einigen weiteren Transformationen können Sie sehen, dass Sie die gewünschte Ausgabe erreicht und die Logik für eine solche Transformation von einer benutzerdefinierten Funktion genutzt haben.After a few more transformations, you can see that you've reached your desired output and leveraged the logic for such a transformation from a custom function.

Endgültige Ausgabe Abfrage nach dem Aufrufen einer benutzerdefinierten Funktion Final output query after invoking a custom function