Einstieg in Power Query Formula Language (M) – Praktisches Beispiel Zeitdimension (Teil 3/3)

In diesem dritten und letzten Beitrag unserer Blog-Reihe zur Power Query Language geht es um die praktische Anwendung: Die meisten Datenmodelle haben einen zeitlichen Bezug und benötigen daher eine Zeitdimension. Bislang war es dafür erforderlich, auf eine Quelle zurückzugreifen, welche die gewünschten Datumswerte und ihre Attribute liefert. Darauf kann mit Power Query verzichtet werden. Anhand des Zeit-Beispiels zeigen wir Schritt für Schritt, wie die bereits vorgestellten Sprach-Elemente eingesetzt werden können.

In diesem dritten und letzten Beitrag unserer Blog-Reihe zur Power Query Language geht es um die praktische Anwendung: Die meisten Datenmodelle haben einen zeitlichen Bezug und benötigen daher eine Zeitdimension. Bislang war es dafür erforderlich, auf eine Quelle zurückzugreifen, welche die gewünschten Datumswerte und ihre Attribute liefert. Darauf kann mit Power Query verzichtet werden. Anhand des Zeit-Beispiels zeigen wir Schritt für Schritt, wie die bereits vorgestellten Sprach-Elemente eingesetzt werden können.

Zu Beginn erzeugen wir eine Liste mit Datumswerten. Wie zuvor aufgezeigt, ist es dafür nicht notwendig, alle Elemente anzugeben. Sie lassen sich auch durch Angabe von Ober- und Untergrenze erzeugen. Auch gibt es Generator-Funktionen, mit deren Hilfe man Listen auf verschiedene Arten erstellen kann (eine Zusammenfassung ist unter diesem Link verfügbar).

Die folgende Funktion zum Beispiel gibt eine Liste von Datumswerten aus, sogar unter Berücksichtigung von Schaltjahren.

Syntax:

 

Hier wird die Eingabe eines Startdatums erwartet und es gilt, das Inkrement für die Länge des Zeitraums zu definieren. Gemeint ist die Intervall-Größe (zum Beispiel Tage, Jahre). Diese Angaben müssen in den entsprechenden Datentypen hinterlegt werden. Dazu werden die beiden Funktionen „Date“ und „Duration“ verwendet. Sie nehmen Zahlen an und erzeugen daraus die benötigten Datentypen:

 

Das Ergebnis ist folgende Abfrage, die für einen Zeitraum von zwei Jahren die Tage liefert:

 

Der nächste Schritt ist nun die Zerlegung des Datums in Jahr, Monat und Tag.

 

Diese Funktion teilt ein Datum in einen Record auf, der die gewünschten Felder (Tag, Monat und Jahr) enthält.

 

Diese Funktion nimmt eine Liste, wendet die übergebene Funktion („transform“) auf jeden Eintrag an und gibt die so veränderte Liste als Ergebnis zurück.

Angewendet auf das Beispiel:

 

Es gibt nun diverse Funktionen, um aus anderen Daten-Typen wiederum eine Tabelle zu erzeugen. Quellen können sein: Rows, Records, Values und eben auch Listen.

Eine solche Funktion erzeugt aus unserer Datumsliste eine Tabelle:

 

Parameter:

 

Als nächstes ergibt sich mithilfe dieser Funktion in unserem Beispiel folgender Code:

 

Das Ergebnis ist eine Tabelle mit drei Spalten:

 

Auf dieser Tabelle können nun viele verschiedene Funktionen angewendet werden.

Mit der folgenden Syntax gelingt es, weitere berechnete Spalten hinzuzufügen:

 

Zuerst werden die Zieltabelle und der neue Spaltenname angegeben. Dann folgt die Funktion, aus der sich der Wert der Spalte ergibt, hier „columnGenerator“ genannt.

Wichtig ist hier das Schlüsselwort „each“. Dies bedeutet, die folgende Funktion/Gleichung soll auf jede Zeile angewendet werden.

Beispiel:

 

Die Spaltennamen werden in eckigen Klammern angegeben. Als Ergebnis wird die neue Tabelle mit der zusätzlichen Spalte zurück geliefert.

Power Query Formula bietet eine Fülle von Datumsfunktionen an. Als erstes Beispiel sei folgende Funktion genannt, welche zu einem Datumswert diverse Bestandteile als Text ausgibt:

 

Der Parameter „formatas“ bestimmt die Ausgabe.

Beispiel:

 

Mit dem Parameter „Culture“ kann auch die Sprache geändert werden.

Dies alles auf das laufende Beispiel angewendet, ergibt folgenden Code:

 

Eigene Funktionen erstellen

Als Beispiel für eigene Funktionen wird diese Datums-Query nun in eine Funktion umgewandelt. Das ermöglicht es, komplexere Code-Stücke wiederholt mit verschiedenen Parametern auszuführen.

Beispiel:

 

Dieses Beispiel nimmt die Parameter x und y an und gibt deren Summe zurück. Bei der Parameterliste ist die Angabe des Datentyps optional, kann aber zur Sicherheit mit angegeben werden, damit falsche Datentypen schon beim Aufruf als Fehler erkannt werden.

Auf das laufende Beispiel angewendet, ergibt sich folgende Abfrage:

 

Nur wird natürlich nicht immer der gleiche Zeitraum benötigt, und vielleicht sollen die Attribute auch mal in einer anderen Sprache angezeigt werden. Also ersetzten wir einfach die festen Werte mit Parametern, die der Funktion beim Aufruf übergeben werden:

 

Wir haben nun gesehen, wie Listen und daraus Tabellen erzeugt, Datumswerte konvertiert und eigene Funktionen erstellt werden. Nach diesem Muster kann der Anwender nun seine Zeit-Dimension den eigenen Anforderungen entsprechend erweitern. Das funktioniert mit String-Operationen sowie der vielseitigen Date.ToText-Funktion.

Als Beispiel für einige weitere Attribut-Spalten sei abschließend folgende Abfrage angeführt:

Philipp Rouaiha

Philipp Rouaiha ist seit mehreren Jahren als Berater im Data Warehouse- und Business Intelligence-Bereich unterwegs. Hier entwickelt er Lösungen auf Basis des Microsoft BI-Stacks und realisiert Datenanbindungen an SAP mithilfe des cMORE-Werkzeugkastens der pmOne. Darüber hinaus engagiert er sich im OneLab Self Service-BI.

Blog-Beiträge von diesem Autor