Power Query hat eine Vielzahl nativer Funktionen zu bieten, die Dir das Leben erleichtern, doch das ist noch lange nicht alles. Es ist auch möglich eigene, sogenannte benutzerdefinierte Funktionen in Power Query zu schreiben. Damit kannst Du Dir die Arbeit erheblich erleichtern, indem Du wiederholt genutzte Funktionalitäten in Funktionen auslagerst/ kapselst und wiederholt aufrufst. Das klingt für Dich interessant? Dann lies weiter, denn ich zeige Dir, wie Du benutzerdefinierte Funktionen in Power Query schreibst.

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

Was sind überhaupt Funktionen?

Jeder Excel-Anwender kennt Funktionen. Sie nehmen in der Regel eine gewisse Anzahl Parameter auf und geben einen Wert zurück. Die Parameter sind dabei entweder Pflichtparameter (auf diese kann für die Ermittlung des Ergebnisses nicht verzichtet werden), oder optionale Parameter, die für das Ergebnis der Funktion nicht zwingend benötigt werden. In seltenen Fällen nehmen Funktionen auch keinen Parameter auf. Ein Beispiel hierfür wäre die Excel-Funktion HEUTE(), die das aktuelle Tagesdatum zurückliefert. Schauen wir uns Funktionen in Excel an.

Funktionen in Excel

Die zu bildende Summe der Summanden 1 und 2 kann auch ohne Funktion über C2+C3 ermittelt werden:

 

Das Ganze hört jedoch auf Spaß zu machen, sobald ich mehr als zwei Werte habe:

 

Abhilfe schafft hier die wohl bekannteste Excel-Funktion: Die Funktion SUMME(). Lass uns einen Blick auf die Funktionsweise dieser Funktion werfen:

Ich muss der Funktion SUMME() nicht alle Parameter einzeln übergeben, denn diese Funktion akzeptiert als Parameter auch einen Bereich von Zellen (in meinem Beispiel der Bereich C2:C11). Funktionen erleichtern uns also das Leben, indem sie nach Übergabe bestimmter Parameter mehr oder weniger komplexe Rechenoperationen für uns durchführen und das Ergebnis (in diesem Beispiel einen einzelnen Wert) zurückliefern. Schauen wir uns das Ganze in Power Query an.

Funktionen in Power Query

In Power Query erfüllen Funktionen dieselbe Aufgabe, wie in Excel, allerdings haben wir hier bei der Art der zurückgelieferten Ergebnisse mehr Optionen. Die gängisten Optionen sind die folgenden.

Ergebnis: Skalarer Wert

Die Kalkulation eines skalaren Wertes, in meinem Beispiel die zeilenweise Summe der Werte Wert1 und Wert2.

Ergebnis: List

Die Kalkulation einer Liste von Werten, in meinem Beispiel eine Liste der Werte von Wert1 bis Wert2.

Ergebnis: Table

Die Kalkulation einer Tabelle, in meinem Beispiel eine Tabelle mit der Spalte Column1 und den Werten von Wert1 bis Wert2.

Ergebnis: Record

Die Kalkulation eines Records, in meinem Beispiel ein Record mit den Felder Feld1 und Feld2 und den Werten aus Spalte Wert1 und Wert2.

Skalare Werte, Listen, Tabellen und Records werden für die unterschiedlichsten weiterführenden Berechnungen benötigt, auf die ich hier nicht weiter eingehen werde. Wichtig sind an dieser Stelle lediglich zwei Erkentnisse:

• Funktionen erfüllen in Power Query die gleiche Aufgabe wie es Funktionen in Excel tun: Sie nehmen Parameter auf, führen auf dessen Basis Berechnungen aus und geben anschließend Werte zurück.
• Rückgabewerte in Power Query können andere Formen haben, als es in Excel der Fall ist.

Dies sind nicht die einzigen Möglichkeiten für Rückgabewerte einer Funktion, aber es sind die geläufigsten.

Wann es sinnhaft ist, eine Benutzerdefinierte Funktionen zu schreiben

Zum gegenwärtigen Zeitpunkt existieren in Power Query knapp 680 Funktionen. Doch was ist, wenn Du andere Funktionalitäten benötigst, die die nativen Funktionen so nicht zur Verfügung stellen? Am besten erklärt sich so etwas an einem Beispiel.

Ein Beispiel: Die Berechnung einer Quersumme

Eine Quersumme ist die Summe der Ziffern einer mehrstelligen Zahl. Die Quersumme von 123 ist also 1+2+3=6. Es gibt in Power Query keine einzelne Funktion, die die Quersumme von Werten kalkuliert. Möchte ich eine Quersumme in Power Query kalkulieren, so muss ich verschiedene Funktionen verschachtelt nutzen. Eine mögliche Lösung sieht wie folgt aus:

Die Funktionsweise dieser Lösung ist nicht Gegenstand der Diskussion, aber ich denke Du bist mit mir darüber einig, dass es ermüdend wäre, immer wieder dieselbe Formel schreiben, oder kopieren zu müssen, falls ich an anderer Stelle erneut die Berechnung einer Quersumme benötigen würde. Jetzt sieh Dir mal die Berechnung der nächsten Quersummen-Spalte an:

Das Ergebnis dieser berechneten Spalte ist deckungsgleich mit der vorherigen, aber die Formel zur Erstellung ist deutlich übersichtlicher, oder?! 😉 Der Grund hierfür ist, dass ich eine benutzerdefinierte Funktion geschrieben habe, die den Namen fnQuersumme trägt. Diese Funktion führt exakt denselben Code aus, wie im ersten Beispiel, jedoch ist dieser Code in eben dieser Funktion gekapselt und kann beliebig oft durch Aufrufen des Funktionsnamens und Übergabe der Spalte Zahl als Parameter wiederverwendet werden: Egal wo ich innerhalb dieser Power Query-Instanz die Bildung einer Quersumme benötige – ich kann mit fnQuersumme(Zahl zur Quersummenbildung) die Quersumme einer Zahl bilden. Die Vorteile einer solchen Funktion sind also:

• Wiederverwendbarkeit gekapselter Funktionalitäten (durch Aufruf des Funktionsnamens)
• Deutlich kürzerer und damit übersichtlicherer Code (Aufruf des Funktionsnamens statt des gesamten Funktionskörpers)

Immer dann, wenn mir auffällt, dass ich bestimmte Codebestandteile aus anderen Abfragen kopiere und mehrfach in bestimmte Abfragen einfüge, beginne ich über die Entwicklung einer entsprechenden Funktion nachzudenken. Doch wie erstellt man die eigentlich?

So schreibst Du eine benutzerdefinierte Funktion

Im Gegensatz zu DAX ist es in Power Query/ M möglich, eigene Funktionen zu schreiben. Die Funktionen können dabei sehr vielfältige Komplexitätsgrade aufweisen. Ich beginne aus didaktischen Gründen mit einer sehr einfache Funktion, ohne jegliche Parameter.

Eine einfache Funktion, ohne Parameter

Hierzu öffne ich in Power Query eine leere Abfrage, indem ich einen Rechtsklick mit der Maus in den Bereich der Abfragen mache und Leere Abfrage auswähle:

Zu allererst gebe ich der Abfrage, die ich zur Funktion umbauen werde, einen Namen (1): fnEinfacheFunktion(). Anschließend öffne ich den Erweiterten Editor (2).

Der Erweiterte Editor zeigt auch bei leeren Abfragen standardmäßig ein leeres Let-Statement. Dieses benötige ich für meine einfache Funktion nicht und überschreibe es daher einfach wie folgt:

Wundere Dich nicht, über die Schreibweise. In Power Query werden benutzerdefinierte Funktionen mit ()=> eingeleitet. Die Aufgabe dieser einfachen Funktion ist es, bei jedem Aufruf die Zeichenkette „Ich bin eine einfache Funktion, ohne Parameter“ zurückzugeben. Bestätige ich die Abfrage nun mit Fertig, dann sieht das Ergebnis wie folgt aus:

Die Abfrage hat nun ein fx als Präfix erhalten, was sie unter den Abfragen optisch als Funktion kennzeichnet. Zudem gibt es jetzt die Schaltfläche Aufrufen, die meine Funktion ausführt und dessen Ergebnis in einer neuen Abfrage ausgibt. Das Ergebnis des Funktionsaufrufes sieht, wenig überraschend, so aus:

Funktionen ohne Parameter sind eher selten, werden aber beispielsweise genutzt, um das aktuelle Tagesdatum widerzugeben. Deutlich praxisrelevanter wird dies, wenn übergebene Parameter innerhalb der Funktion verarbeitet werden.

Eine einfache Funktion, mit Parameter

Ich nehme die vorherige Abfrage als Basis, lege mir von dieser Abfrage eine Kopie an und nenne diese fnEinfacheFunktionMitParameter(). Im Erweiterten Editor passe ich die Funktion wie folgt an:

Im Vergleich zur vorherigen Funktion habe ich zwei Änderungen vorgenommen:

1. Zwischen der öffnenden und schließenden Klammer habe ich den Parameter Parameteranzahl eingefügt und ihm den Datentyp number zugewiesen. Damit ist die Funktion in der Lage diesen Parameter aufzunehmen und weiterzuverwenden.
2. Ich habe diesen Parameter in den Funktionskörper integriert. Da Power Query Texte und Zahlen nicht miteinander kombinieren kann, habe ich die Parameteranzahl durch Number.ToText(Parameteranzahl) in einen Text konvertiert, bevor ich diesen mit der gesamten Meldung (über &-Zeichen, wie in Excel) verknüpfe.

Über die Nutzeroberfläche kann ich der Funktion die Parameteranzahl übergeben (in meinem Beispiel 5) und kann sehen, wie der Parameter in den Rückgabewert integriert wird:

Damit habe ich die Funktionsweise von benutzerdefinierten Funktionen erläutert. Allerdings entfalten diese Funktionen zumeist erst dann ihr volles Potential, wenn sie innerhalb anderer Abfragen eingesetzt werden. Was ich damit meine, zeige ich Dir nun.

Funktionen in anderen Abfragen aufrufen

Bisher habe ich die geschriebenen Funktionen nur über die Nutzeroberfläche aufgerufen. Einen viel praktischeren Nutzen bekommt eine benutzerdefinierte Funktion jedoch, wenn man sie in anderen Abfragen nutzt. Ausgangspunkt ist eine Abfrage, die eine Spalte AnzParam beinhaltet, deren Werte nummerisch sind und von 1 bis 10 gehen. Ich kann nun (wie vorhin schon beim Beispiel mit der Quersumme gezeigt), eine neue benutzerdefinierte Spalte hinzufügen, in welcher meine gerade erstellte benutzerdefinierte Funktion aufgerufen wird. Ich übergebe ihr die Werte aus Spalte AnzParam als Parameter. Dieser Funktionsaufruf findet in der Tabelle automatisch zeilenweise statt, so dass in jeder Zeile ein anderer Wert (1, 2… 10) an die Funktion übergeben wird. Der Rückgabewert ist somit je Zeile verschieden.

Benutzerdefinierte Funktionen sind Bestandteil vieler Programmiersprachen. Vielleicht kennst Du das Prinzip auch schon aus VBA. Falls benutzerdefinierte Funktionen für Dich ein vollkommen neues Konzept sind, wirst Du wahrscheinlich einen Moment brauchen, um es zu verdauen. Das ist vollkommen normal. Ich kann Dir nur empfehlen, Dich mit diesem Thema zu beschäftigen und Dir über die Zeit Deine eigene Funktionsbibliothek anzulegen.

Diese Funktionen speichere ich dann in einer leeren Exceldatei (oder auch Power BI Desktop-Datei) und nutze dies stets als Startpunkt für neue Projekte. Das spart Zeit, minimiert Fehler und ist professionell 😉

Fazit

Hier nochmal das Wichtigste auf einen Blick:

• Funktionen kapseln Funktionalitäten und machen diese so leicht wiederverwendbar
• Ihr Aufruf verhindert, dass Code immer und immer wieder neu geschrieben/ kopiert werden muss und macht die Abfrage übersichtlicher
• Funktionen werden durch ()=> eingeleitet
• Sie können Parameter aufnehmen, diese im Funktionskörper verarbeiten und damit den Rückgabewert beeinflussen
• Häufig verwendete Funktionen sollten in eine Funktionsbibliothek münden, die dann Startpunkt neuer Projekte ist

In meinem nächsten Beitrag werde ich zeigen, wie Du komplexere benutzerdefinierte Funktionen schreiben kannst.

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 🙂