Wiederkehrende Aufgaben packt man am besten in Funktionen. In Power Query kann man nicht nur Abfragen, sondern eigenen, wiederverwendbare Funktionen anlegen. Komplexes Thema – hier eine einfache, nicht zu technische Einführung.

M – die Syntax in Power Query

Wenn Sie in Power Query eine Abfrage anlegen und verschiedene Transformationen auf den Daten ausführen, dann wird intern in Power Query alles in eine Skript-Sprache umgesetzt. Ein bisschen so, wie wenn man in Excel ein Makro aufnimmt und man sich dann den VBA-Quellcode ansiehst.

Wenn Sie aus der Adventure Works Datenbank die Tabelle SalesOrderHeader importieren und nur relevante Spalten wählen (lesen Sie in diesem Artikel, warum Sie möglichst keine Spalten löschen sollten), dann sieht die Abfrage so aus:

Das Skript finden Sie 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) haben Sie die Tabelle Sales\SalesOrderHeader_ gewählt und deren Daten (die Schritte hat der Assistent zum Öffnen der Access-Datenbank für Sie generiert). Anschließend haben Sie 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 werden Sie häufig ein # gefolgt von Anführungszeichen sehen. Sie können zwar Quelle als Variable ohne # angeben. Aber niemals Zweiter Schritt. Da ist ein Leerzeichen drin. Daher müssen Sie diese Variable so angeben: #"Zweiter Schritt".

Die nächsten Funktionsaufrufe erfolgen (meistens) auf Basis der Ergebnisse des vorherigen Schritts. Daher dürfen Sie 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

Sie können eine Abfrage auch ohne externe Anbindung formulieren. Und Sie müssen auch keine Tabelle zurückgeben.

Beispielsweise können Sie zwei fixe Zahlen addieren. Dazu legen Sie in Power Query eine Leere Abfrage an (Neue Abfrage – Andere Quellen – Leere Abfrage). Sie finden im erweiterten Editor das Gerüst einer Abfrage:

Die ändern Sie wie folgt ab:

Wenn Sie die Abfrage schließen, erhalten Sie 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 haben Sie 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 Sie die Abfrage jetzt schließen, dann sehen Sie keine Daten mehr, sondern einen Dialog, der zwei Parameter von dir haben möchte:

Funktionsdialog in Power Query

Funktion ausführen = neue Abfrage

Wenn Sie jetzt zwei Zahlen für x und y eingeben und Aufrufen wählen, 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 Sie den erweiterten Editor der neuen Abfrage öffnen, sehen Sie, 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, definieren Sie in der Funktion bei den Parametern den gewünschten Typ:

Wenn Sie die Funktion jetzt nochmal ansehen, dann steht im Dialog zur Eingabe der Parameter nicht mehr optional und dass es sich um Zahlen handeln muss. Wenn Sie mehr über Datentypen lernen wollen, dann schauen Sie sich unseren Artikel über Datentypen in Power Query an.

Das Gute bei Datentypen: Wenn Sie beispielsweise ein Datum haben möchten und das entsprechend angeben, dann erhalten Sie 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. Sie müssen die Formeln durch Einrückungen unbedingt strukturieren. Sonst kommt man völlig durcheinander. Hier hilft Ihnen auch unsere Web-App Power Query Formatter.

Damit die Funktion von anderen Abfragen mit dem Namen GetValueAfterTax aufgerufen werden kann, müssen Sie 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. Geben Sie einfach im Dialog 100 ein. Als Ergebnis sollten Sie 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 legen Sie eine neue benutzerdefinierte Spalte an (Spalte hinzufügen – Benutzerdefinierte Spalte). Im Dialog zur Berechnung der benutzerdefinierten Spalte können Sie jetzt auf die Funktion zugreifen:

Dialog für benutzerdefinierte Spalten

Sie hätten auch direkt Benutzerdefinierte Funktion aufrufen wählen können. Das kommt auf das gleiche raus. In der neuen Spalte sollten Sie 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 Ihren Daten mit Ihren 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. ==> mehr zu unseren Leistungen

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

Interessiert? Dann schreiben Sie uns oder rufen uns am besten gleich an!