Im letzten Beitrag habe ich darüber geschrieben was Funktionen eigentlich sind und wie Du Deine erste einfache benutzerdefinierte Funktion in Power Query/ M schreibst. Im heutigen Beitrag möchte ich Dir zeigen, wie Du auch komplexere benutzerdefinierte Funktionen in Power Query schreiben kannst. Je komplexer die Funktion, desto größer ist die Wahrscheinlichkeit, dass der Code hin und wieder angepasst werden muss. Wie das geht, zeige ich Dir in diesem Beitrag 🙂

Als Abonnent meines Newsletters erhältst Du die Beispieldateien zu den Beiträgen dazu. Hier geht’s zum Abonnement des Newsletters!

Das Szenario

Um eine etwas komplexere Funktion zeigen zu können, muss es auch eine Problemstellung geben, die es zu lösen gilt. Stell Dir folgendes Szenario vor. Ich bekomme monatlich eine txt-Datei aus einer anderen Abteilung zugesandt, die ich zu analysieren habe. Leider liegen die Daten nicht in der gewünschten Form vor, die ich für meine Analysen benötige. Daher nehme ich Power Query zu Hilfe und erstelle eine Lösung, die mir die Datenlieferung in eine nützliche, flache Tabelle verwandelt:

Diese Tabelle exportiere ich anschließend in eine Exceltabelle für die weiterführende Analyse.

Der nächste Monat

Mit dem neuen Monat erfolgt eine erneute Datenlieferung, die in ein neues Tabellenblatt in Excel geladen werden soll. Es gibt wie immer mehrere Lösungsmöglichkeiten. Ein valider Ansatz ist eine Funktion, welche den Pfad der zu transformierenden txt-Datei aufnimmt und dann die gewünschte Tabellenstruktur erzeugt, so wie hier:

Pfad zur Datenquelle eingeben, bestätigen und die Daten liegen in der benötigten Form vor. Sieht das für Dich interessant aus? Schauen wir uns an, wie das geht.

Die Erstellung der benutzerdefinierten Funktion

Um diese benutzerdefinierte Funktion zu erstellen, dupliziere ich die Abfrage Datenlieferung, durch einen Rechtsklick auf die Abfrage → Duplizieren. Der neuen Abfrage gebe ich den Namen fnTableTransform, wobei der Präfix fn für Funktion steht. Jetzt werde ich diese Abfrage in eine Funktion umwandeln. Hierzu gehe ich in den Erweiterten Editor der Abfrage, um Einsicht in den dahinterliegenden M-Code zu erhalten:

Werfen wir einen Blick auf den M-Code…

Der Blick in den Erweiterten Editor

Der erste Blick in den Erweiterten Editor dürfte für Dich verwirrend sein.

M ist eine funktionale Sprache, dessen Logik sich mir beim ersten Betrachten damals nicht auf Anhieb erschloss. Ich möchte hier daher kurz die notwendigen Dinge erläutern:

• Der gesamte Code ist in Schritte unterteilt, die alle innerhalb eines Let-in-Statements definiert sind
• Jeder Schritt hat einen Namen, z. B. Quelle, oder auch #"Geänderter Typ"
• Schritte bauen zumeist (aber nicht zwangsläufig) aufeinander auf. Daher sieht man es häufig, dass der Name eines Schrittes im darauffolgenden Schritt innerhalb der Funktionen benutzt wird (siehe rote und gelbe Markierung im Screenshot).
• Derjenige Schritt, der nach dem in benannt ist (letzte Zeile im Skript), stellt das Ergebnis der gesamten Abfrage da. Das Ergebnis dieses Schrittes wird dem Nutzer – zumeist, aber nicht notwendigerweise – als Tabelle zurückgegeben. In meinem Beispiel ist dies der grün markierte Schritt #"Geänderter Typ1".

So viel kurz zum Verständnis des M-Codes. Diesen Code baue ich jetzt zu einer Funktion um.

Den bestehenden Code zu einer Funktion umgestalten

Ich hatte bereits im letzten Beitrag gezeigt, dass eine benutzerdefinierte Funktion in M mit ()=> eingeleitet wird. Zudem hatte ich gezeigt, dass zwischen diesen beiden Klammern die Aufnahme eines (oder mehrerer) Parameter definiert werden kann. Meine Funktion benötigt einen Parameter, der den Pfad der zu transformierenden Datei aufnehmen und an die Funktion weiterreichen kann. Das Ganze funktioniert wie folgt:

Durch das Voranstellen von (FileFullPath as text) as table =>  gestalte ich die Abfrage in eine Funktion um, die einen Parameter (FileFullPath) vom Typ text aufnehmen kann. Der Name FileFullPath ist hierbei völlig frei wählbar, sollte aber sprechend für Dich sein. Mit as table definiere ich, dass der Rückgabewert der Funktion vom Typ Tabelle sein muss. Ich möchte ja schließlich die transformierte Tabelle in Excel einfügen und nutzen können. Auf der Nutzeroberfläche äußert sich mein Handeln so:

Das sieht bis hierhin schon mal sehr vielversprechend aus. Ich teste die Funktion an dieser Stelle, indem ich den Dateipfad zur August-Datei einfüge und auf Aufrufen klicke.

Ich bekomme zwar eine transformierte Tabelle zurückgeliefert, aber wenn Du genau hinsiehst, dann fällt Dir auf, dass die Funktion die Juli-Datei zurückgeliefert hat, obwohl ich den Pfad für die August-Datei übergeben habe. Die Ursache hierfür ist schnell gefunden: Ich habe zwar den Parameter für die Funktion definiert, diesen jedoch noch nicht in den Funktionskörper eingebunden. Hier steht der zu nutzende Pfad zur Datei immer noch als feste Zeichenkette drin:

Die Lösung ist recht einfach. Ich muss die Zeichenkette durch den Parameter ersetzen und diesen damit in den Funktionskörper einbinden.

Die Parameter in den Funktionskörper einbinden

Um den Inhalt des Parameters an die Funktion übergeben zu können, muss der fest definierte Pfad innerhalb des Schrittes Quelle durch den Namen des Parameters (bei mir FileFullPath) ersetzt werden.

Jetzt liefert die Funktion die gewünschte Datei zurück.

Wir haben jetzt das gewünschte Ergebnis erzielt. Aber eine Frage stellt sich jetzt noch: Wie kann ich die Funktion bei Bedarf anpassen?

So passt Du Deine Funktion an

Du hast bereits gesehen, dass das Ergebnis einer aufgerufenen Funktion in einer separaten Abfrage zurückgegeben wird und nicht in der Abfrage, die die Funktion definiert. Jetzt möchte ich meine Funktion jedoch leicht verändern und sofort sehen, wie sich die Veränderung auf den Rückgabewert der Funktion auswirkt. Mit der bisherigen Lösung müßte ich nach jeder Anpassung der Funktion in die Abfrage „Aufgerufene Funktion“ gehen um zu sehen, wie sie sich ausgewirkt hat. Somit würde ich ständig zwischen Funktion und Abfrage hin und herspringen. Das ist ziemlich lästig. Ich zeige Dir einen besseren Weg.

Eine Funktion in einer Abfrage umwandeln

Wir haben zu Beginn eine Abfrage in eine Funktion konvertiert… Dies geht natürlich auch andersherum 🙂

Indem ich „//“ vor den funktionsdefinierenden Teil setze (1), wandle ich die Funktion zurück in eine gewöhnliche Abfrage. Mit „//“ werden Zeilen innerhalb des M-Codes auskommentiert, d. h. diese Zeilen werden nicht bei der Ausführung der Funktion berücksichtigt. Auf diese Weise kann man auch Code-Zeilen in M kommentieren, was die Lesbarkeit des Codes stark verbessert. Bestätige ich jetzt mit Fertig, erhalte ich jedoch eine Fehlermeldung, weil der Schritt Quelle immer noch nach dem Parameter sucht, den ich aber gerade auskommentiert habe. Um diesen Fehler abzufangen, füge ich zu Beginn einen Schritt mit dem Namen des Parameters ein (2) und weisen diesem den Pfad zur entsprechenden Datei zu. Auf diese Weise kann die Datei korrekt importiert werden. Bestätige ich jetzt mit Fertig, kann ich mir das Ergebnis eines jeden einzelnen Schrittes, wie für Abfragen üblich, ansehen.

Jetzt möchte ich meine Funktion dahingehend anpassen, dass die erste Spalte ‚Monat und Jahr‘ und zwei Spalten aufgeteilt wird: ‚Jahr‘ und ‚Monat‘.

Die inhaltliche Anpassung der Funktion vornehmen

Um die bestehende Spalte in zwei Spalten aufzuteilen und diese Spalten umzubenennen, kann ich mich Standardfunktionalitäten von Power Query bedienen. Hierzu gehe ich wie folgt vor:

• Ich markiere die Spalte ‚Monat und Jahr‘
• Dann klicke ich auf Transformieren → Spalten teilen  → Nach Trennzeichen → Leerzeichen
• Danach benenne ich die beiden neuen Spalten entsprechend um

Das Ergebnis sieht dann wie folgt aus:

Die Anpassung umfasst drei neue Schritte (rot gerahmt) und liefert das gewünschte Ergebnis. Der letzte Schritt, der jetzt noch zu unternehmen ist, ist die Rückverwandlung der Abfrage in eine Funktion.

Die Abfrage in eine Funktion zurückverwandeln

Um aus der Funktion eine Abfrage zu machen, habe ich den funktionsdefinierenden Teil auskommentiert und einen Schritt eingefügt, der den Namen des Parameters trug. Das mache ich jetzt natürlich umgekehrt. Ich entferne die beiden „//“ vor dem Funktionskopf und kommentiere den Schritt FileFullPath mit „//“ aus. Diesen könnte ich auch löschen, aber vielleicht brauche ich den bei der nächsten Anpassung nochmal. 😉 Wie Du sehen kannst (rot gerahmt), hat die Funktion jetzt die drei Schritte mehr, die ich vorhin definiert habe.

Rufe ich die Funktion von nun an auf, ist die Spalte ‚Jahr und Monat‘ von vornherein in zwei Spalten aufgeteilt.

Bis zum nächsten Mal und denk dran: Sharing is caring. Wenn Dir der Beitrag gefallen hat, dann teile ihn gerne. Falls Du Anmerkungen hast, schreibe gerne einen Kommentar, oder schicke mir eine Mail an lars@ssbi-blog.de

Viele Grüße aus Hamburg,

Lars

Ich schreibe meine Beiträge für Dich, den Leser. Bitte schenke mir eine Minute Deiner Zeit und bewerte die folgenden Kategorien, um mir zu helfen meine Beiträge so gut wie möglich zu schreiben. Danke 🙂