Benutzerdefinierte Funktionen verwenden

Wenn Sie sich in einer Situation befinden, in der Sie dieselbe Reihe von Transformationen auf verschiedene Abfragen oder Werte anwenden müssen, kann es von Vorteil sein, eine benutzerdefinierte Power Query-Funktion zu erstellen, die so oft wie nötig wiederverwendet werden kann. Eine benutzerdefinierte Power Query-Funktion ist eine Zuordnung von einer Reihe von Eingabewerten zu einem einzelnen Ausgabewert und wird aus nativen M-Funktionen und Operatoren erstellt.

Sie können zwar eine eigene benutzerdefinierte Power Query-Funktion mithilfe von Code manuell erstellen, wie unter Grundlegendes zu Power Query M-Funktionen beschrieben, doch die Power Query-Benutzeroberfläche bietet Features, um das Erstellen und Verwalten einer benutzerdefinierten Funktion zu beschleunigen, zu vereinfachen und zu verbessern.

Dieser Artikel konzentriert sich auf diese Erfahrung, die nur über die Power Query-Benutzeroberfläche bereitgestellt wird, und darauf, wie Sie sie optimal nutzen können.

Wichtig

In diesem Artikel wird beschrieben, wie Sie mit Power Query eine benutzerdefinierte Funktion mithilfe allgemeiner Transformationen erstellen, auf die über die Power Query-Benutzeroberfläche zugegriffen werden kann. Er konzentriert sich auf die Kernkonzepte zum Erstellen benutzerdefinierter Funktionen und enthält Links zu weiteren Artikeln in der Power Query-Dokumentation für weitere Informationen zu bestimmten Transformationen, auf die in diesem Artikel verwiesen wird.

Eine benutzerdefinierte Funktion aus einem Tabellenverweis erstellen

Hinweis

Das folgende Beispiel wurde mit der Desktop-Erfahrung in Power BI Desktop erstellt und kann auch mit der Power Query-Erfahrung in Excel für Windows verfolgt werden.

Sie können diesem Beispiel folgen, indem Sie die in diesem Artikel verwendeten Beispieldateien über den folgenden Downloadlink herunterladen. Der Einfachheit halber wird in diesem Artikel der Ordner-Konnektor verwendet. Weitere Informationen zum Ordner-Connector finden Sie unter Ordner. Das Ziel dieses Beispiels besteht darin, eine benutzerdefinierte Funktion zu erstellen, die auf alle Dateien in diesem Ordner angewendet werden kann, bevor alle Daten aller Dateien in einer einzigen Tabelle zusammengefasst werden.

Beginnen Sie mit der Verwendung des Ordner-Connectors, um zu dem Ordner zu navigieren, in dem sich Ihre Dateien befinden, und wählen Sie Daten transformieren oder Bearbeiten aus. Mit diesen Schritten gelangen Sie zu Power Query. Klicken Sie mit der rechten Maustaste auf den binären Wert Ihrer Wahl im Feld Inhalt, und wählen Sie die Option Als neue Abfrage hinzufügen aus. In diesem Beispiel wurde die erste Datei in der Liste ausgewählt, bei der es sich zufällig um die Datei April 2019.csv handelt.

Mit dieser Option wird praktisch eine neue Abfrage mit einem Navigationsschritt erstellt, der direkt zu dieser Datei als Binärdatei führt. Der Name dieser neuen Abfrage entspricht dem Dateipfad der ausgewählten Datei. Benennen Sie diese Abfrage in Beispieldatei um.

Erstellen Sie einen neuen Parameter mit dem Namen Dateiparameter und dem Typ Binär. Verwenden Sie die Abfrage Beispieldatei als Standardwert und als aktuellen Wert.

Hinweis

Es wird empfohlen, den Artikel zu Parametern zu lesen, um besser zu verstehen, wie Parameter in Power Query erstellt und verwaltet werden.

Benutzerdefinierte Funktionen können mit jedem Parametertyp erstellt werden. Es ist nicht erforderlich, dass eine benutzerdefinierte Funktion eine Binärdatei als Parameter hat.

Der binäre Parametertyp wird nur im Dropdownmenü Typ des Dialogfelds Parameter angezeigt, wenn Sie eine Abfrage haben, die als Binärdatei ausgewertet wird.

Es ist möglich, eine benutzerdefinierte Funktion ohne Parameter zu erstellen. Dies wird häufig in Szenarien gezeigt, in denen eine Eingabe aus der Umgebung abgeleitet werden kann, in der die Funktion aufgerufen wird. Beispielsweise eine Funktion, die das aktuelle Datum und die aktuelle Uhrzeit der Umgebung übernimmt und aus diesen Werten eine bestimmte Textzeichenfolge erstellt.

Klicken Sie im Bereich Abfragen mit der rechten Maustaste auf Dateiparameter. Wählen Sie die Option Verweis aus.

Benennen Sie die neu erstellte Abfrage aus dem Dateiparameter (2) in Transformationsbeispieldatei um.

Klicken Sie mit der rechten Maustaste auf diese neue Abfrage Transformationsbeispieldatei, und wählen Sie die Option Funktion erstellen aus.

Hiermit wird praktisch eine neue Funktion erstellt, die mit der Abfrage Transformationsbeispieldatei verknüpft wird. Änderungen, die Sie an der Abfrage Transformationsbeispieldatei vornehmen, werden automatisch in der benutzerdefinierten Funktion repliziert. Verwenden Sie während der Erstellung dieser neuen Funktion die Transformationsdatei als Funktionsnamen.

Nachdem Sie die Funktion erstellt haben, wird eine neue Gruppe mit dem Namen Ihrer Funktion für Sie erstellt. Diese neue Gruppe enthält Folgendes:

• Alle Parameter, auf die in Ihrer Abfrage Transformationsbeispieldatei verwiesen wurde.
• Ihre Abfrage Transformationsbeispieldatei, die häufig als Beispielabfrage bezeichnet wird.
• Die neu erstellte Funktion, in diesem Fall Transformationsdatei.

Transformationen auf eine Beispielabfrage anwenden

Wählen Sie mit der erstellten neuen Funktion die Abfrage mit dem Namen Transformationsbeispieldatei aus. Diese Abfrage ist jetzt mit der Funktion Transformationsdatei verknüpft, sodass sich Änderungen an dieser Abfrage in der Funktion widerspiegeln. Diese Verbindung wird als Konzept einer mit einer Funktion verknüpften Beispielabfrage bezeichnet.

Die erste Transformation, die bei dieser Abfrage durchgeführt werden muss, ist eine Transformation, die die Binärdatei interpretiert. 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.

Das Format aller CSV-Dateien im Ordner ist identisch. Sie haben alle eine Kopfzeile, die die ersten vier obersten Zeilen umfasst. Die Spaltenüberschriften befinden sich in Zeile 5, und die Daten beginnen in Zeile 6, wie im nächsten Bild gezeigt.

Der nächste Satz an Transformationsschritten, die auf die Transformationsbeispieldatei angewendet werden müssen, sind:

1. Entfernen der obersten vier Zeilen – Hiermit werden die Zeilen entfernt, die als Teil des Headerabschnitts der Datei betrachtet werden.

   

   Hinweis

   Weitere Informationen zum Entfernen von Zeilen oder Filtern einer Tabelle nach Zeilenposition finden Sie unter Nach Zeilenposition filtern.

2. Kopfzeilen höher stufen – Die Kopfzeilen für die endgültige Tabelle befinden sich jetzt in der ersten Zeile der Tabelle. Sie können diese, wie in der nächsten Abbildung dargestellt, höher stufen.

   

Nach Höherstufung der Spaltenüberschriften fügt Power Query standardmäßig automatisch einen neuen Schritt Geänderter Typ hinzu, der die Datentypen für jede Spalte automatisch erkennt. Die Abfrage Transformationsbeispieldatei ähnelt der im nächsten Bild.

Hinweis

Weitere Informationen zum Höher- und Tieferstufen von Kopfzeilen finden Sie unter Höher- oder Tieferstufen von Spaltenüberschriften.

Achtung

Die Funktion Transformationsdatei basiert auf den Schritten, die in der Abfrage Transformationsbeispieldatei ausgeführt werden. Wenn Sie jedoch versuchen, den Code für die Funktion Transformationsdatei manuell zu ändern, erhalten Sie gleich zu Beginn die folgende Warnung: 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'.

Eine benutzerdefinierte Funktion als neue Spalte aufrufen

Da die benutzerdefinierte Funktion jetzt erstellt und alle Transformationsschritte einbezogen sind, können Sie zur ursprünglichen Abfrage mit der Liste der Dateien aus dem Ordner zurückkehren (in diesem Beispiel CSV-Dateien). Wählen Sie auf der Registerkarte Spalte hinzufügen im Menüband die Option Benutzerdefinierte Funktion aufrufen aus der Gruppe Allgemein aus. Geben Sie im Fenster Benutzerdefinierte Funktion aufrufen den Begriff Ausgabetabelle als neuen Spaltennamen ein. Wählen Sie den Namen Ihrer Funktion Transformationsdatei aus dem Dropdownmenü Funktionsabfrage aus. Nachdem Sie die Funktion im Dropdown-Menü ausgewählt haben, wird der Parameter für die Funktion angezeigt. Sie können dann auswählen, welche Spalte aus der Tabelle als Argument für diese Funktion verwendet werden soll. Wählen Sie die Spalte Inhalt als Wert/Argument aus, das für den Dateiparameter übergeben werden soll.

Nach Auswahl von OK wird eine neue Spalte mit dem Namen Ausgabetabelle erstellt. Diese Spalte enthält Tabellenwerte in ihren Zellen, wie in der nächsten Abbildung dargestellt. Entfernen Sie aus Gründen der Einfachheit alle Spalten aus dieser Tabelle mit Ausnahme von Name und Ausgabetabelle.

Hinweis

Weitere Informationen zum Auswählen oder Entfernen von Spalten aus einer Tabelle finden Sie unter Spalten auswählen oder entfernen.

Ihre Funktion wurde auf jede einzelne Zeile aus der Tabelle angewendet, wobei die Werte aus der Spalte Inhalt als Argument für Ihre Funktion verwendet werden. Da die Daten nun in die gewünschte Form umgewandelt werden, können Sie die Spalte Ausgabetabelle erweitern, indem Sie das Symbol „Erweitern“ auswählen. Verwenden Sie kein Präfix für die erweiterten Spalten.

Sie können überprüfen, ob Daten aus allen Dateien im Ordner vorhanden sind, indem Sie die Werte in der Spalte Name oder Datum überprüfen. In diesem Fall können Sie die Werte aus der Spalte Datum überprüfen, da jede Datei nur Daten für einen einzelnen Monat aus einem bestimmten Jahr enthält. Wenn mehrere angezeigt werden, bedeutet dies, dass Sie Daten aus mehreren Dateien erfolgreich in einer einzelnen Tabelle kombiniert haben.

Hinweis

Das, was Sie bisher gelesen haben, ist grundsätzlich derselbe Prozess, der während der Erfahrung Dateien kombinieren geschieht, aber manuell durchgeführt wird.

Es wird empfohlen, auch den Artikel Übersicht über das Kombinieren von Dateien und CSV-Dateien kombinieren zu lesen, um weiter zu verstehen, wie die Benutzeroberfläche der kombinierten Dateien in Power Query funktioniert und welche Rolle benutzerdefinierte Funktionen spielen.

Einen neuen Parameter zu einer vorhandenen benutzerdefinierten Funktion hinzufügen

Stellen Sie sich vor, dass es zusätzlich zur gerade erstellten Funktion eine neue Anforderung gibt. Demnach müssen Sie vor dem Kombinieren der Dateien die Daten darin filtern, um ausschließlich die Zeilen abzurufen, in denen das Land gleich Panama ist.

Um diese Anforderung zu erfüllen, erstellen Sie einen neuen Parameter namens Markt mit dem Textdatentyp. Geben Sie für den aktuellen Wert den Wert Panama ein.

Wählen Sie mit diesem neuen Parameter die Abfrage Transformationsbeispieldatei aus, und filtern Sie das Feld Land mithilfe des Werts aus dem Parameter Markt.

Hinweis

Weitere Informationen zum Filtern von Spalten nach Werten finden Sie unter Filterwerte.

Wenn Sie diesen neuen Schritt auf die Abfrage anwenden, wird die Funktion Transformationsdatei automatisch aktualisiert. Dies erfordert nun zwei Parameter entsprechend den beiden Parametern, die in der Transformationsbeispieldatei verwendet werden.

Aber neben der Abfrage CSV-Dateien befindet sich ein Warnzeichen. Da die Funktion jetzt aktualisiert ist, sind zwei Parameter erforderlich. Der Schritt, in dem Sie die Funktion aufrufen, führt also zu Fehlerwerten, da nur eines der Argumente an die Funktion Transformationsdatei während des Schritts Benutzerdefinierte Funktion aufgerufen übergeben wurde.

Um die Fehler zu beheben, doppelklicken Sie in Benutzerdefinierte Funktion aufgerufen in den angewendeten Schritten, um das Fenster Benutzerdefinierte Funktion aufrufen zu öffnen. Geben Sie im Parameter Markt manuell den Wert Panama ein.

In den angewendeten Schritten können Sie nun zur erweiterten Ausgabetabelle zurückkehren. Überprüfen Sie die Abfrage dahingehend, ob nur Zeilen, in denen Land gleich Panama ist, im endgültigen Ergebnissatz der Abfrage CSV-Dateien angezeigt werden.

Eine benutzerdefinierte Funktion aus einer wiederverwendbaren Logik erstellen

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. Später kann diese benutzerdefinierte Funktion für die Abfragen oder Werte Ihrer Wahl aufgerufen werden. Diese benutzerdefinierte Funktion könnte Ihnen Zeit sparen und Ihnen bei der Verwaltung Ihrer Transformationen an einer zentralen Stelle helfen, die Sie jederzeit ändern können.

Stellen Sie sich beispielsweise eine Abfrage vor, die mehrere Codes als Textzeichenfolge enthält. Sie möchten eine Funktion erstellen, die diese Werte dekodiert, wie in der folgenden Beispieltabelle dargestellt:

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

Sie beginnen mit einem Parameter, der einen Wert hat, der als Beispiel dient. In diesem Fall ist es der Wert PTY-CM1090-LAX.

Aus diesem Parameter erstellen Sie eine neue Abfrage, auf die Sie die gewünschten Transformationen anwenden. In diesem Fall möchten Sie den Code PTY-CM1090-LAX in mehrere Komponenten aufteilen:

• Ursprung = PTY
• Ziel = LAX
• Airline = CM
• Flugnummer = 1090

Der folgende M-Code veranschaulicht den Satz an Transformationen.

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-Formelsprache.

Sie können diese Abfrage dann in eine Funktion umwandeln, indem Sie mit der rechten Maustaste auf die Abfrage klicken und Funktion erstellen auswählen. Schließlich können Sie Ihre benutzerdefinierte Funktion in jeder Ihrer Abfragen oder Werte aufrufen, wie in der nächsten Abbildung gezeigt.

Nach einigen weiteren Transformationen können Sie feststellen, dass die gewünschte Ausgabe erreicht ist und die Logik für eine derartige Transformation anhand einer benutzerdefinierten Funktion angewendet wurde.