Power QueryEigene M-Funktionen erstellen und anwenden
In Power Query gibt es viele vordefinierte Funktionen – aber manchmal benötigt man wiederverwendbare Berechnungen, die es so noch nicht gibt. Genau dafür bieten Power Query und die dort genutzte M-Sprache die Möglichkeit, eigene Funktionen zu schreiben.
In diesem Beitrag lernen Sie Schritt für Schritt, wie man
- eine eigene M-Funktion erstellt,
- Parameter übergibt und deren Datentypen absichert und
- die Funktion direkt in einer Abfrage aufruft.
Beispiel: Berechnung des Bruttobetrags aus einem Nettopreis und einem variablen Mehrwertsteuersatz
Sie haben in Excel eine Tabelle mit Nettopreisen und Mehrwertsteuersatz angelegt und diese Tabelle in Power Query importiert. Das Ergebnis kann dann folgendermaßen aussehen.
Das Ziel: Der Bruttobetrag soll berechnet werden – und zwar mit einer wiederverwendbaren Power-Query-Funktion. So müssen Sie nicht jedes Mal neu rechnen.
Schritt 1: Leere Abfrage zur Funktionsdefinition erstellen
Aktivieren Sie in Power Query die folgende Befehlsfolge: Start > Neue Quelle > Andere Quellen > Leere Abfrage
Es wird eine leere Abfrage in Power Query geöffnet.
Öffnen Sie den Erweiterten Editor, indem Sie im Menüband die Befehlsfolge Start > Erweiterter Editor aktivieren.
Ersetzen Sie den Standardcode durch:
(bruttoNetto as number, mwstSatz as number) as number =>
bruttoNetto * (1 + mwstSatz)
Erklärung des M-Codes
(bruttoNetto as number, mwstSatz as number)
Dies ist die Eingabe der Funktion. Sie erwartet zwei Parameter:
- bruttoNetto: der Nettopreis
- mwstSatz: der Mehrwertsteuersatz
Beide Parameter sind vom Typ number. Das heißt, es dürfen nur Zahlen übergeben werden.
as number hinter der Klammer
Dies sichert zusätzlich ab, dass auch der Rückgabewert eine Zahl sein muss.
=>
Der sogenannte „Gleich-Groß“-Operator leitet die Berechnungslogik der Funktion ein.
bruttoNetto * (1 + mwstSatz)
Die Berechnungsformel: Aus Nettopreis und Mehrwertsteuersatz wird der Bruttobetrag berechnet.
Beispiel: Gibt man 100 und 0.19 ein, so ergibt sich: 100 * (1 + 0.19) = 119
Hinweis: In Power Query werden Dezimalzahlen mit einem . (Punkt) als Dezimaltrennzeichen eingegeben.
Schließen Sie den erweiterten Editor, indem Sie auf die Schaltfläche Fertig klicken.
Schritt 2: Funktion benennen und testen
Speichern Sie die Abfrage unter dem Namen fx_Brutto.
Geben Sie im Power-Query-Fenster über das Feld Parameter eingeben beispielhafte Werte ein (zum Beispiel 100 und 0.19) und klicken Sie auf Abrufen.
Power Query zeigt Ihnen sofort das Ergebnis 119.
Wenn Sie einen falschen Datentyp eingeben (zum Beispiel Text), erhalten Sie eine Fehlermeldung – das ist der Vorteil der expliziten Datentyp-Definition!
Schritt 3: Funktion in Tabelle anwenden
Nun wenden Sie Ihre Funktion auf die Tabelle an. Aktivieren Sie zunächst die Abfrage (Tabelle1), in der Sie die Funktion einsetzen wollen.
Klicken Sie im Menüband auf die Befehlsfolge Spalte hinzufügen > Benutzerdefinierte Spalte.
Es öffnet sich das Dialogfeld Benutzerdefinierte Spalte. Vergeben Sie den Spaltennamen: Bruttobetrag.
Im Formelfeld schreiben Sie:
=fx_Brutto([Nettopreis],[#"MwSt-Satz"])
Erklärung:
- fx_Brutto ist der Name Ihrer benutzerdefinierten Funktion.
- [Nettopreis] und [#"MwSt-Satz"] sind Spaltennamen Ihrer Tabelle.
Da MwSt-Satz ein Minuszeichen im Namen hat, muss der Feldname in Anführungszeichen mit einem vorangegangenen #-Zeichen erfasst werden.
Tipp: Am besten übernehmen Sie die Namen direkt per Doppelklick aus dem Bereich Verfügbare Spalten. Der Name wird hierdurch automatisch entsprechend in der Formel angepasst.
Die Funktion wird für jede Zeile automatisch mit den jeweiligen Werten aufgerufen.
Klicken Sie auf OK – und schon sehen Sie die neue Spalte mit den berechneten Bruttobeträgen.
Fazit
Mit einer eigenen M-Funktion in Power Query können Sie:
- Berechnungen zentral pflegen und wiederverwenden
- Die Funktion direkt in Tabellen anwenden
- Durch Datentypen absichern, dass Ihre Datenstruktur korrekt bleibt
Gerade im Controlling, bei wiederkehrenden Kalkulationen oder bei Datenimporten lohnt es sich, eigene Funktionen zu erstellen – korrekt, skalierbar und schnell.
