In Power Query kann man nicht nur Abfragen, sondern auch benutzerdefinierte Funktionen anlegen, die man wiederverwenden kann. Komplexes Thema – wir versuchen eine einfache, nicht zu technische Einführung!

M – die Syntax in Power Query

Wenn du in Power Query eine Abfrage anlegst und verschiedene Transformationen auf den Daten ausführst, dann wird intern in Power Query alles in eine Skript-Sprache umgesetzt. Ein bisschen so, wie wenn du in Excel ein Makro aufnimmst und dir dann den VBA-Quellcode dazu ansiehst.

Wenn du aus der Adventure Works Datenbank die Tabelle SalesOrderHeader importierst und nur relevante Spalten wählst (lies in diesem Artikel, warum du nie Spalten löschen darfst), dann sieht die Abfrage so aus:

Das Skript findest du im erweiterten Editor. Entweder über das Menü Start – Erweiterter Editor oder Ansicht – Erweiterter Editor.

Der erste Schritt (Quelle) öffnet die Access-Datenbank. Die Funktion gibt alle Tabellen (und Sichten) der Access-Datenbank zurück. Im zweiten Schritt (_Sales_SalesOrderHeader) hast du die Tabelle Sales\SalesOrderHeader_ gewählt und deren Daten (die Schritte hat der Assistent zum Öffnen der Access-Datenbank für dich generiert). Anschließend hast du Spalten ausgewählt (Andere entfernte Spalten). Und genau diesen Stand gibt die Abfrage oben wieder zurück. Die Rückgabe ist also eine Tabelle mit Spalten und Zeilen.

let/in

Eine Abfrage beginnt mit dem Schlüsselwort let. Dann folgen Funktionsaufrufe, deren Ergebnisse in die Variable auf der linken Seite gespeichert werden. Wenn die Variablen Sonderzeichen oder Leerzeichen enthalten, müssen die Namen Escaped werden. Das bedeutet, man muss Power Query mitteilen, wo der Variablen-Name anfängt und wo er aufhört. Daher wirst du häufig ein # gefolgt von Anführungszeichen sehen. Du kannst zwar Quelle als Variable ohne # angeben. Aber niemals Zweiter Schritt. Da ist ein Leerzeichen drin. Daher musst du diese Variable so angeben: #"Zweiter Schritt".

Die nächsten Funktionsaufrufe erfolgen (meistens) auf Basis der Ergebnisse des vorherigen Schritts. Daher darfst du die Zeilen nicht einfach vertauschen. Am Ende aller Funktionsaufrufe folgt das Schlüsselwort in gefolgt von der Variable, dessen Ergebnis man zurückgeben möchte.

Eine ganz einfache Abfrage

Du kannst eine Abfrage auch ohne externe Anbindung formulieren. Und du musst auch keine Tabelle zurückgeben.

Beispielsweise kannst du zwei fixe Zahlen addieren. Dazu legst du in Power Query eine Leere Abfrage an (Neue Abfrage – Andere Quellen – Leere Abfrage). Du findest im erweiterten Editor das Gerüst einer Abfrage:

Die änderst du wie folgt ab:

Wenn du die Abfrage schließt erhältst du als Ergebnis "2" zurück. Hier handelt es sich um eine einfache Rückgabe - keine Tabelle mit Spalten und Zeilen.

Eine Funktion zurückgeben

Auf diese Weise kann man auch eine Funktion zurückgeben. Klingt etwas seltsam. Man gibt eben keinen Wert oder Tabelle zurück, sondern die Vorschrift, wie Eingaben in Ausgaben umgewandelt werden sollen. Eine Funktion eben.

In Mathe hast du mal gelernt, dass man eine Funktion, die zwei Zahlen addiert, in folgender Form definieren kann

(x,y) => x+y

Genauso formulieren wir das auch in Power Query:

Wenn du die Abfrage jetzt schließt, dann siehst du keine Daten mehr, sondern einen Dialog, der zwei Parameter von dir haben möchte:

Funktionsdialog in Power Query

Funktion ausführen = neue Abfrage

Wenn du jetzt zwei Zahlen für x und y eingibst und Aufrufen wählst, dann wird eine neue Abfrage angelegt mit dem Ergebnis der Addition. Die Ausführung der Funktion ist selbst natürlich wieder eine Abfrage - eine Abfrage, die unsere neue Funktion verwendet.

Wenn du den erweiterten Editor der neuen Abfrage öffnest, siehst du, wie der Funktionsaufruf erfolgt:

Datentypen

x und y haben den Datentyp any. Das ist der Default-Datentyp, wenn nichts anderes angegeben wurde. Damit könnte man der neuen Funktion auch "Hallo" und "Welt" als Parameter übergeben. Was aber zu einem Fehler führen würde, weil man Texte nicht addieren kann. Um die beiden Parameter nicht mehr optional und typsicher (ich will nur Zahlen) zu machen, definierst du in der Funktion bei den Parametern den gewünschten Typ:

Wenn du die Funktion jetzt nochmal ansiehst, dann steht im Dialog zur Eingabe der Parameter nicht mehr optional und dass es sich um Zahlen handeln muss. Wenn du mehr über Datentypen lernen willst, dann schau dir meinen Nachschlage-Artikel für Datentypen an.

Das Gute bei Datentypen: Wenn Du beispielsweise ein Datum haben möchtest und das entsprechend angibst, dann erhältst du im Funktionsdialog einen Datepicker, um das Datum komfortabel auszuwählen.

Komplexe Funktionen

Die oben dargestellte Funktion kann mit einem Ausdruck definiert werden: x+y. Was aber, wenn in der Funktion mehrere Transformationen ausgeführt werden sollen? Definieren wir eine Funktion GetValueAfterTax die zu einem übergebenen Wert die Steuer addiert. Die Funktion blähen wir absichtlich etwas auf:

Wenn Funktionen also aus mehreren Schritten bestehen, werden diese selbst wieder mit let eingeführt und mit in und der gewünschten Ergebnis-Variable abgeschlossen. Du musst die Formeln durch Einrückungen unbedingt strukturieren. Sonst kommt man völlig durcheinander.

Damit die Funktion von anderen Abfragen mit dem Namen GetValueAfterTax aufgerufen werden kann, musst du die Abfrage, die diese Funktion zurückgibt, entsprechend benennen (die Variable MyFunc im M-Skript taucht außerhalb nirgendwo mehr auf):

Benutzerdefinierte Funktion umbenennen

Verwendung der Funktion

Über den Dialog in Power Query kann man Funktionen sehr gut testen. Gib einfach im Dialog 100 ein. Als Ergebnis solltest du 119 zurückbekommen. Sonst ist etwas falsch.

Sagen wir die SalesOrderHeader Tabelle aus der Adventure Works Datenbank hat nur ein Feld SubTotal für den Nettobetrag des Auftrags. In einer Funktion möchten wir den Betrag inkl. Steuern zurückgeben. Dazu legst du eine neue benutzerdefinierte Spalte an (Spalte hinzufügen – Benutzerdefinierte Spalte). Im Dialog zur Berechnung der benutzerdefinierten Spalte kannst du jetzt auf die Funktion zugreifen:

Dialog für benutzerdefinierte Spalten

Du hättest auch direkt Benutzerdefinierte Funktion aufrufen wählen können. Das kommt auf das gleiche raus. In der neuen Spalte solltest du jetzt die Werte für den Wert inklusive Steuern sehen.

Beispiele für Funktionen

  • Datumstabelle: Eine Kalendertabelle kann man auch in Power Query erstellen lassen. Und dafür schreibt man sich am besten eine Funktion die das Start- und das Enddatum als Parameter erhält.
  • Mapping: Oft muss man Mappings ausführen – Niederlassungen mit Kürzel A sollen mit Kürzel B ins Datenmodell geladen werden.
  • Lookups: Eine Funktion kann dafür verwendet werden in einer anderen Abfrage etwas nachzuschlagen
  • Parameter in Excel: Im Prinzip eine Lookup-Funktion – die Parameter werden in Excel gepflegt und in Power Query beispielsweise zur Filterung oder zur Einschränkung von Datenmengen verwendet. Oder schlicht um Dateipfade zu hinterlegen, damit man an zentraler Stelle einstellen kann, wo sich Quelldateien befinden.

Alles Inputs für folgende Posts :-)

Unterstützung gewünscht?

Wir werden oft gefragt, ob wir nur Standard-Trainings abhalten. Natürlich machen wir mehr als das. Wir unterstützen in Workshops auf deinen Daten mit deinen Auswertungen. Wir setzen Reports auch eigenständig für unsere Kunden um, wenn die eigenen Mitarbeiter knapp sind oder unsere Kunden sich mehr Geschwindigkeit wünschen. Durch unsere langjährige Erfahrung verstehen wir vorhandene Datenstrukturen und Anforderungen unserer Kunden sehr schnell.

Wir bieten auch die Möglichkeit des Supports, damit du, deine Kollegen oder deine Mitarbeiter einen Ansprechpartner bei Problemen haben.

Interessiert? Dann schreib uns oder ruf uns am besten gleich an!