Verwenden von benutzerdefinierten Funktionen
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 benutzerdefinierte Power Query-Funktion erstellen, die so oft wie nötig wiederverwendet werden kann. Eine benutzerdefinierte Power Query-Funktion ist eine Zuordnung aus einer Reihe von Eingabewerten zu einem einzelnen Ausgabewert und wird aus systemeigenen M-Funktionen und Operatoren erstellt.
Sie können ihre eigene benutzerdefinierte Power Query-Funktion manuell erstellen, indem Sie die Power Query M-Formelspracheverwenden, oder 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.
Zunächst befassen wir uns mit den grundlegenden Schritten für das Erstellen einer benutzerdefinierten Funktion, indem wir Code in der Benutzeroberflächeschreiben. Dann werden wir uns darauf konzentrieren, die Schnittstelle zu nutzen, um komplexe Aktionen in eine wiederverwendbare Funktionumzuwandeln.
Wichtig
In diesem Artikel wird beschrieben, wie Sie eine benutzerdefinierte Funktion mit Power Query mithilfe allgemeiner Transformationen erstellen, auf die auf der Power Query-Benutzeroberfläche zugegriffen werden kann. Es konzentriert sich auf die Kernkonzepte zum Erstellen von benutzerdefinierten Funktionen und Links zu anderen Artikeln in der Power Query-Dokumentation, um weitere Informationen zu bestimmten Transformationen zu erhalten, auf die in diesem Artikel verwiesen wird.
Erstellen einer benutzerdefinierten Funktion aus Code in der Benutzeroberfläche
Anmerkung
Die folgenden Schritte können in Power BI Desktop oder mithilfe der Power Query-Oberfläche in Excel für Windows befolgt werden.
- Verwenden Sie die Connector-Erfahrung, um eine Verbindung zu Ihren Daten herzustellen, wo sie gespeichert sind. Wenn Sie Ihre Daten ausgewählt haben, wählen Sie die Schaltfläche Daten transformieren oder Bearbeiten aus. Dadurch gelangen Sie zum Power Query-Erlebnis.
- Klicken Sie im Bereich Abfragen auf der linken Seite mit der rechten Maustaste auf eine leere Stelle.
- Wählen Sie Leere Abfrage aus.
- Wählen Sie im neuen leeren Abfragefenster das Menü Start aus und anschließend Erweiterter Editor.
- Ersetzen Sie die Vorlage durch Ihre benutzerdefinierte Funktion. Zum Beispiel:
let HelloWorld = () => ("Hello World") in HelloWorld - Wählen Sie Fertig aus.
Weitere Informationen zum Entwickeln benutzerdefinierter Funktionen mit der Formelsprache Power Query M finden Sie in diesem Artikel: Grundlegendes zu Power Query M-Funktionen. In den folgenden Abschnitten gibt es Lernprogramme, in denen beschrieben wird, wie Sie die Power Query-Benutzeroberfläche verwenden können, um benutzerdefinierte Funktionen zu entwickeln, ohne Code zu schreiben, und Anweisungen dazu, wie Sie Ihre benutzerdefinierte Funktion in Ihrer Abfrage aufrufen können.
Erstellen Sie eine benutzerdefinierte Funktion aus einer Anleitung zur Tabellenreferenz.
Anmerkung
Das folgende Beispiel wurde mithilfe der Desktopoberfläche in Power BI Desktop erstellt und kann auch mithilfe der Power Query-Oberfläche in Excel für Windows befolgt werden.
Sie können diesem Beispiel folgen, indem Sie die in diesem Artikel verwendeten Beispieldateien aus dem folgenden Downloadlinkherunterladen. Der Einfachheit halber verwendet dieser Artikel den Ordnerkonnektor. Weitere Informationen zum Ordner-Connector finden Sie unter Ordner. Ziel dieses Beispiels ist das Erstellen einer benutzerdefinierten Funktion, die auf alle Dateien in diesem Ordner angewendet werden kann, bevor alle Daten aus allen Dateien in einer einzigen Tabelle kombiniert 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. Diese Schritte führen Sie zur Power Query-Oberfläche. Klicken Sie mit der rechten Maustaste auf den Wert Binär Ihrer Wahl im Feld Inhalt, und wählen Sie die Option Als neue Abfrage hinzufügen aus. In diesem Beispiel wurde die erste Datei aus der Liste ausgewählt, nämlich die Datei April 2019.csv.
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 Beispieldateium.
Erstellen Sie einen neuen Parameter mit dem Namen Dateiparameter und Typ von Binär. Verwenden Sie die Beispieldateiabfrage als Standardwert und Aktueller Wert.
Anmerkung
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 Parametertyp „Binär“ wird nur im Dropdownmenü Typ des Dialogfelds Parameter angezeigt, wenn Sie eine Abfrage haben, die zu einer 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 verwendet, und erstellt eine bestimmte Textzeichenfolge aus diesen Werten.
Klicken Sie im Bereich Abfragen mit der rechten Maustaste auf Dateiparameter. Wählen Sie die Option Verweis aus.
Benennen Sie die neu erstellte Abfrage von 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.
Mit diesem Vorgang wird effektiv eine neue Funktion erstellt, die mit der Transformationsbeispieldatei Abfrage verknüpft ist. Alle Änderungen, die Sie an der Transformationsbeispieldatei und der Abfrage vornehmen, werden automatisch in Ihre benutzerdefinierte Funktion übertragen. Verwenden Sie bei der Erstellung dieser neuen Funktion die Transform-Datei als den Funktionsnamen.
Beachten Sie nach dem Erstellen der Funktion, dass eine neue Gruppe für Sie mit dem Namen Ihrer Funktion erstellt wird. Diese neue Gruppe enthält:
- Auf alle Parameter, auf die in der AbfrageTransformationsbeispieldatei verwiesen wurde.
- Ihre Abfrage Transformationsbeispieldatei, eher bekannt als Beispielabfrage.
- Die neu erstellte Funktion, in diesem Fall Transformationsdatei.
Anwenden von Transformationen auf eine Beispielabfrage
Wählen Sie mit der erstellten neuen Funktion die Abfrage mit dem Namen Transformationsbeispieldateiaus. Diese Abfrage ist jetzt mit der Transformdatei Funktion verknüpft, sodass alle an dieser Abfrage vorgenommenen Änderungen in der Funktion widerzuspiegeln sind. Diese Verbindung wird als Konzept einer Beispielabfrage bezeichnet, die mit einer Funktion verknüpft ist.
Die erste Transformation, die mit dieser Abfrage geschehen muss, ist eine, die die Binärdatei interpretiert. Sie können im Vorschaubereich mit der rechten Maustaste auf die Binärdatei klicken und die Option CSV- auswählen, um die Binärdatei als CSV-Datei zu interpretieren.
Das Format aller CSV-Dateien im Ordner ist identisch. Sie haben alle eine Überschriften, die die ersten vier obersten Zeilen umfasst. Die Spaltenüberschriften befinden sich in Zeile 5, und die Daten beginnen mit Zeile 6 nach unten, wie in der nächsten Abbildung dargestellt.
Die nächsten Transformationsschritte, die auf die Transformationsbeispieldatei angewendet werden müssen sind:
Entfernen Sie die obersten vier Zeilen– Diese Aktion entfernt die Zeilen, die als Teil des Kopfzeilenabschnitts der Datei betrachtet werden.
Anmerkung
Weitere Informationen zum Entfernen von Zeilen oder Filtern einer Tabelle nach Zeilenposition finden Sie unter Filtern nach Zeilenposition.
Höherstufen von Überschriften – Die Überschriften für die endgültige Tabelle befinden sich jetzt in der ersten Zeile der Tabelle. Sie können diese höher stufen, wie in der nächsten Abbildung dargestellt.
Nachdem Sie die Spaltenüberschriften höher gestuft haben, fügt Power Query standardmäßig den neuen Schritt Geänderter Typ hinzu, der die Datentypen für jede Spalte automatisch erkennt. Ihre Abfrage Transformationsbeispieldatei sieht wie im nächsten Bild gezeigt aus.
Anmerkung
Weitere Informationen zum Höher- und Tieferstufen von Überschriften finden Sie unter Höherstufen oder Tieferstufen von Spaltenüberschriften.
Vorsicht
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 diese 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'..
Aufrufen einer benutzerdefinierten Funktion als neue Spalte
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 (CSV-Dateien in diesem Beispiel). 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 die Ausgabetabelle als Neuer 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 Inhaltsspalte als Wert/Argument aus, das für den Dateiparameterübergeben werden soll.
Nachdem Sie OKausgewählt haben, wird eine neue Spalte mit dem Namen Ausgabetabelle erstellt. Diese Spalte enthält Werte der --Tabelle in ihren Zellen, wie in der nächsten Abbildung dargestellt. Aus Gründen der Einfachheit entfernen Sie alle Spalten aus dieser Tabelle mit Ausnahme Name und Ausgabetabelle.
Anmerkung
Weitere Informationen zum Auswählen oder Entfernen von Spalten aus einer Tabelle finden Sie unter Auswählen oder Entfernen von Spalten.
Ihre Funktion wurde auf jede einzelne Zeile der Tabelle angewendet, wobei die Werte aus der Spalte Inhalt als Argument für die Funktion dienten. Nachdem die Daten nun in das gewünschte Shape umgewandelt werden, können Sie die Ausgabetabelle Spalte 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 Spalte "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 mehr als eins angezeigt wird, bedeutet dies, dass Sie Daten aus mehreren Dateien erfolgreich in eine einzelne Tabelle kombiniert haben.
Anmerkung
Was Sie bisher gelesen haben, ist grundsätzlich derselbe Prozess, der während der Kombinieren von Dateien Erfahrung geschieht, aber manuell durchgeführt wird.
Es wird empfohlen, dass Sie auch den Artikel zu Übersicht über das Kombinieren von Dateien und Kombinieren von CSV-Dateien lesen, um weiter zu verstehen, wie die Benutzeroberfläche für die Kombination von Dateien in Power Query funktioniert und welche Rolle benutzerdefinierte Funktionen spielen.
Hinzufügen eines neuen Parameters zu einer vorhandenen benutzerdefinierten Funktion
Stellen Sie sich vor, dass es eine neue Anforderung zusätzlich zu dem gibt, was Sie derzeit gebaut haben. 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 Market mit dem Textdatentyp. Geben Sie für den Aktuellen Wertden Wert Panamaein.
Wählen Sie mit diesem neuen Parameter die Abfrage Transformationsbeispieldatei aus, und filtern Sie das Feld Land mithilfe des Werts aus dem Parameter Markt.
Anmerkung
Weitere Informationen zum Filtern von Spalten nach Werten finden Sie unter Filterwerte.
Wenn Sie diesen neuen Schritt auf Ihre Abfrage anwenden, wird die Funktion der Transformationsdatei automatisch aktualisiert, die jetzt zwei Parameter benötigt, basierend auf den zwei Parametern, die Ihre Beispieldatei Transformation verwendet.
Die CSV-Dateien--Abfrage weist jedoch ein Warnzeichen neben sich auf. Nachdem Ihre Funktion aktualisiert wurde, sind zwei Parameter erforderlich. Der Schritt, in dem Sie die Funktion aufrufen, führt also zu Fehlerwerten, da während des Schritts Aufrufen der benutzerdefinierten Funktion nur eines der Argumente an die Funktion Transformationsdatei übergeben wurde.
Um die Fehler zu beheben, doppelklicken Sie auf Benutzerdefinierte Funktion aufrufen unter Angewendete Schritte, um das Fenster Benutzerdefinierte Funktion aufrufen zu öffnen. Geben Sie im parameter Market manuell den Wert Panamaein.
Sie können nun zur Erweiterten Ausgabetabelle unter Angewendete Schritte zurückkehren. Überprüfen Sie Ihre Abfrage, um sicherzustellen, dass nur Zeilen, in denen Land gleich Panama ist, im endgültigen Ergebnis der Abfrage von CSV-Dateien enthalten sind.
Erstellen einer benutzerdefinierten Funktion aus einer wiederverwendbaren Logik
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 die benutzerdefinierte Funktion auf die von Ihnen ausgewählten Abfragen oder Werte angewendet werden. Diese benutzerdefinierte Funktion kann Ihnen Zeit sparen und Ihnen bei der Verwaltung Ihrer Transformationen an einem zentralen Ort helfen, die Sie jederzeit ändern können.
Stellen Sie sich beispielsweise eine Abfrage mit mehreren Codes als Textzeichenfolge vor, und Sie möchten eine Funktion erstellen, die diese Werte decodiert, wie in der folgenden Beispieltabelle gezeigt:
| code |
|---|
| PTY-CM1090-LAX |
| LAX-CM701-PTY |
| PTY-CM4441-MIA |
| MIA-UA1257-LAX |
| LAX-XY2842-MIA |
Sie beginnen mit einem Parameter, der einen Wert aufweist, der als Beispiel dient. In diesem Fall ist es der Wert PTY-CM1090-LAX-.
Aus diesem Parameter erstellen Sie eine neue Abfrage, in der Sie die benötigten Transformationen anwenden. In diesem Fall möchten Sie den Code PTY-CM1090-LAX- in mehrere Komponenten aufteilen:
- Ursprung = PTY
- Ziel- = LAX
- Airline = CM
- FlightID = 1090
Der folgende M-Code veranschaulicht den Satz von 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"
Anmerkung
Weitere Informationen zur Power Query M-Formelsprache finden Sie in Power Query M-Formelsprache.
Anschließend können Sie diese Abfrage in eine Funktion umwandeln, indem Sie mit der rechten Maustaste auf die Abfrage klicken und Funktion erstellenauswählen. Schließlich können Sie Ihre benutzerdefinierte Funktion in einer ihrer Abfragen oder Werte aufrufen, wie in der nächsten Abbildung dargestellt.
Nach einigen weiteren Transformationen können Sie sehen, dass Sie die gewünschte Ausgabe erreicht und die Logik für eine solche Transformation aus einer benutzerdefinierten Funktion angewendet haben.
