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 Ihre eigene benutzerdefinierte Power Query-Funktion manuell mithilfe von Code erstellen, wie unter Grundlegendes zu Power Query M-Funktionen dargestellt, aber die Power Query-Benutzeroberfläche bietet Ihnen Features, um den Prozess zum 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 verwendet dieser Artikel den Ordner-Connector. 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. Dadurch gelangen Sie zur Power Query-Erfahrung. 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 sehen Sie, dass die Auswahl für die erste Datei aus der Liste getroffen wurde, bei der es sich zufällig um die Datei April 2019.csv handelt.

Diese Option erstellt effektiv eine neue Abfrage mit einem Navigationsschritt direkt zu dieser Datei als Binärdatei, und der Name dieser neuen Abfrage ist der Dateipfad der ausgewählten Datei. Benennen Sie diese Abfrage in Beispieldatei um.

Erstellen Sie einen neuen Parameter mit dem Namen Dateiparameter. Verwenden Sie die Abfrage Beispieldatei als aktuellen Wert, wie in der folgenden Abbildung dargestellt.

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.

Dieser Vorgang erstellt effektiv eine neue Funktion, die mit der Abfrage Transformationsbeispieldatei verknüpft wird. Alle Änderungen, die Sie an der Abfrage Transformationsbeispieldatei vornehmen, werden automatisch in Ihre benutzerdefinierte Funktion repliziert. Verwenden Sie während der Erstellung dieser neuen Funktion die Transformationsdatei als Funktionsnamen.

Nach dem Erstellen der Funktion werden Sie feststellen, dass eine neue Gruppe für Sie mit dem Namen Ihrer Funktion erstellt wird. 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 alle Änderungen, die an dieser Abfrage vorgenommen wurden, in der Funktion widergespiegelt werden. Dies ist das Konzept einer Beispielabfrage, die mit einer Funktion verknüpft ist.

Die erste Transformation, die bei dieser Abfrage durchgeführt werden muss, ist eine, 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 fünf und die Daten beginnen in Zeile sechs abwärts, wie im nächsten Bild gezeigt.

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

1. Die obersten vier Zeilen entfernen – Diese Aktion entfernt die Zeilen, die als Teil des Kopfzeilenabschnitts 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.

   

Power Query fügt standardmäßig automatisch einen neuen Schritt Geänderter Typ hinzu, nachdem die Spaltenüberschriften höhergestellt wurden, die die Datentypen für jede Spalte automatisch erkennen. Ihre Abfrage Transformationsbeispieldatei sieht wie im nächsten Bild aus.

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

Nachdem die benutzerdefinierte Funktion jetzt erstellt wurde und alle Transformationsschritte integriert wurden, können Sie zur ursprünglichen Abfrage zurückkehren, in der Sie über die Liste der Dateien aus dem Ordner verfügen. 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 aus dem Dropdownmenü 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. 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. Nachdem die Daten nun in die gewünschte Form umgewandelt wurden, können Sie die Spalte Ausgabetabelle erweitern, wie in der Abbildung unten dargestellt, ohne Verwendung eines Präfixes 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 eine einzelne 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 zu dem, was Sie erstellt haben, eine neue Anforderung gibt. Die neue Anforderung erfordert, dass Sie vor dem Kombinieren der Dateien die Daten darin filtern, um nur 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 Ihre Abfrage anwenden, wird die Funktion Transformationsdatei automatisch aktualisiert. Dies erfordert nun zwei Parameter basierend auf den beiden Parametern, die ihre Transformationsbeispieldatei verwendet.

Aber neben der Abfrage CSV-Dateien befindet sich ein Warnzeichen. Nachdem Ihre Funktion aktualisiert wurde, 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.

Sie können nun Ihre Abfrage überprüfen, um zu validieren, 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, und Sie möchten eine Funktion erstellen, die diese Werte dekodiert, wie in der folgenden Beispieltabelle:

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 dies 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 M-Code für diese Gruppe von Transformationen wird unten angezeigt.

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 ein paar weiteren Transformationen sehen Sie, dass Sie die gewünschte Ausgabe erreicht haben und die Logik für eine solche Transformation aus einer benutzerdefinierten Funktion nutzen können.