Eigene Funktionen in Power Query erstellen

In Power Query steht eine Vielzahl von Funktionen zur Verfügung.

Allerdings findet sich nicht jede Funktion, die man zum Beispiel aus Excel kennt, in Power Query wieder. Als Beispiel sei genannt die Funktion NETTOARBEITSTAGE, welche in Excel die Anzahl von Werktagen abzüglich einer selbst definierten Anzahl von Feier- und Ferientagen liefert. Hierzu am Ende des Artikels ein Link und weitere Erläuterungen.
Hier soll es zunächst darum gehen, wie man eine eigene Funktion überhaupt im Power-Query Editor anlegt und wie man diese dann benutzt.

Im Folgenden schreiben wir eine sehr einfache Funktion, welche eine gegebene Zahl verdoppelt. Heißen wird die Funktion später fVerdopple. Übergebe ich der Funktion die Zahl 2 als Parameter, erhalte ich 4. So weit, so einfach.

Vorbereitung

Um das Beispiel so einfach wie möglich zu belassen, erstellen wir in Power BI auf dem Reiter Start über die Schaltfläche „Daten eingeben“ eine Tabelle.

Der Tabelle geben wir die Spalte „Zahlen“ und füllen sie mit den Werten 1, 2, 3 und 4. Dann laden wir die Tabelle mit der Schaltfläche „Laden“ ins Datenmodell.

Jetzt wechseln wir über die Schaltfläche Daten transformieren in den Power Query-Editor.

Die Funktion erstellen

Um die Funktion zu erstellen, klicken wir im Power Query-Editor im Reiter Start auf „Neue Quelle“. Dann wählen wir „Leere Abfrage“ aus.

Schließlich klicken wir mit der rechten Maustaste auf den Namen der Abfrage „Abfrage1“ und wählen aus dem Kontextmenü „Erweiterter Editor“.

Im erweiterten Editor geben wir folgenden Code ein (dies ist keine Einführung in M, deshalb gebe ich hier keine Erklärungen zum Code ab)

(zahl as number) as number =>
    let
        verdoppelt = zahl * 2
    in
        verdoppelt

Wenn wir auf „Fertig“ klicken, erstellt Power Query die Funktion zunächst unter dem Namen Abfrage1. Wir benennen diese noch um in „fVerdopple“.

Es öffnet sich ein Dialog, in dem wir unsere Funktion testen können. Wenn wir bei „zahl“ nun 3 eingeben und auf „Aufrufen“ klicken, erhalten wir das korrekte Ergebnis 6.

Verwenden der Funktion

Wie verwenden wir nun die neue, eigene Funktion in Power Query?

In unserer Beispieltabelle haben wir ja die Spalte „Zahlen“. Jetzt wollen wir eine Spalte ins Datenmodell hinzufügen, welche „verdoppelte Zahlen“ heißt.

Im Power Query-Editor klicken wir auf „Spalte hinzufügen“ und wählen bei den Abfragen unsere Tabelle aus.

Nun gibt es zwei Möglichkeiten.

Möglichkeit 1:

Wir wählen die Schaltfläche „Benutzerdefinierte Funktion aufrufen“ aus.

Es öffnet sich ein Fenster, in welchem wir drei Angaben machen: bei „Neuer Spaltenname“ geben wir „verdoppelte Zahlen“ ein oder wie auch immer wir unsere neue Spalte nennen möchten. Bei „Funktionsabfrage“ wählen wir unsere Funktion „fVerdopple“ aus. Bei „zahl“ schließlich wählen wir die Spalte „Zahlen“ unserer Tabelle aus.

Nach einem Klick auf OK haben wir schließlich unsere neue Spalte „verdoppelte Zahlen“

Als Datentyp wird hier zunächst Text vorausgewählt. Es empfiehlt sich, hier den Datentyp noch auf Ganze Zahl zu ändern.

Möglichkeit 2

Bei Möglichkeit 2 wählen wir im Power Query-Editor unter Spalte hinzufügen die Schaltfläche „Benutzerdefinierte Spalte“ aus.

Im sich öffnenden Dialog geben wir bei Neuer Spaltenname wiederum „verdoppelte Zahlen“ ein.

Bei „Benutzerdefinierte Spaltenformel“ geben wir nach dem schon vorhandenen =-Zeichen nur ein „f“ ein und bekommen schon unsere Funktion „fVerdopple“ angeboten. Wir wählen diese aus.
Der Funktion übergeben wir noch aus dem Feld „Verfügbare Spalten“ die Spalte „Zahlen“.

Nach einem Klick auf OK haben wir wieder die neue Spalte verdoppelte Zahlen.

Anwendung in der Praxis – Beispiel: Funktion Nettoarbeitstage

Wofür kann man so eine eigene Funktion brauchen?

Wie in der Einleitung schon angedeutet, wollte ich kürzlich in Power BI die Funktion NETTOARBEITSTAGE verwenden, wie ich es aus Excel gewohnt bin.

Hierzu habe ich eine Tabelle definiert in welcher alle Feiertage (in meinem Fall aus Baden-Württemberg) und der Betriebsurlaub aufgelistet sind.

Beim Verwenden der Funktion NETTOARBEITSTAGE in Excel wird ein Start- sowie ein Enddatum sowie die Liste der so definierten freien Tage übergeben.

Das Ergebnis ist die Anzahl der Tage zwischen Start- und Enddatum bereinigt um die freien Tage.

Nur: Eine Funktion NETTOARBEITSTAGE oder NETWORKDAYS gibt es in Power BI nicht – nicht in DAX und nicht in Power Query.

Bei meiner Recherche im Netz bin ich darauf gestoßen, dass die Power BI – Entwicklerin Imke Feldmann den Code ihrer eigenen Funktion NETWORKDAYS für Power Query veröffentlicht hat.

Zu finden ist der Artikel und der Code unter folgendem Link:
https://www.thebiccountant.com/2020/02/20/date-networkdays-function-for-power-query-and-power-bi/

Wenn Ihr die Funktion von Imke Feldmann verwenden möchtet, erstellt in Eurem Projekt wie oben beschrieben eine leere Abfrage, öffnet den erweiterten Editor und kopiert den Code der Funktion in den Editor.

Die Funktion steht Euch dann wie von mir oben beschrieben zur Verfügung. Ich konnte die Funktion auf Anhieb verwenden und sie leistet mir bereits in mehreren Projekten treue Dienste.

Auch andere Entwickler stellen ihre eigenen Funktionen im Netz zur freien Verwendung zur Verfügung – und vielleicht schreibt man ja auch die eine oder andere Funktion mal selbst in M.

[Update 15.07.2022]
Mit dem Juli-Update von Power BI Desktop hat Microsoft nun die DAX-Funktion NETWORKDAYS zur Verfügung gestellt.