Datentransformation mit Power Query - Ein Beispiel aus der Praxis

Hallo zusammen,

ich habe Power Query viel zu lange viel zu wenig Aufmerksamkeit geschenkt, weil mir lange nicht klar war, dass der Fokus dieses ETL-Tools auf der Datentransformation liegt. Von den Transformationsfähigkeiten von Power Query bin ich mittlerweile mehr als begeistert und möchte Euch an einem realen Problem teilhaben lassen, mit dem ein Freund vor kurzem bei mir vorbeikam. Uwe, ich leihe mir mal exemplarisch Dein Problem :)

Abbildung 1 visualisiert den vorzunehmenden Transformationsprozess. Während die Ausgangstabelle einen Datensatz pro Projekt enthält und sowohl das Projektstartdatum, als auch das Projektendedatum ausweist, soll nach der Transformation für jeden einzelnen Projekttag je ein Datensatz existieren und zudem alle weiteren Informationen zum jeweiligen Projekt (Projektleiter, Projektbezeichnung) enthalten.

Abbildung 1: Der geplante Transformationsprozess

In Excel wären hier mindestens einige Zeilen VBA-Code notwendig, bzw. irgendwelche in T-SQL geschriebene Stored Procedures, sofern die Datenbasis im MS SQL Server befindlich wäre.

In Power Query ist die Lösung faszinierend einfach zu bewerkstelligen und sehr performant. Abbildung 2 zeigt, wie man die Basistabelle aus Excel als Datenquelle für Power Query benutzt.

Abbildung 2: Die Basistabelle aus Excel in Power Query laden

Nachdem die Tabelle in Power Query geladen wurde, sollte als nächstes der Datentyp der beiden Spalten Projektstart und Projektende in Ganze Zahl geändert werden, damit die Differenz zwischen Start- und Endedatum berechnet werden kann. Wie dies funktioniert, zeigt Abbildung 3.

Abbildung 3: Die Datentypen der Datumsspalten in Ganzzahl ändern

Als nächster Schritt erfolgt die Kalkulation der Differenz zwischen Start- und Enddatum in Form einer Liste. Eine Liste ist eine Auflistung von Werten, die in geschweiften Klammern und durch Kommata separiert dargestellt werden. Hier ein Beispiel für eine Liste in Power Query: {1,2,3}

Abbildung 4: Einfügen einer neuen berechneten Spalte

Um eine Liste, basierend auf vorhandenen Spalten zu generieren, ist die Funktion List.Numbers() das richtige Instrument. Hierbei ist auf zwei Dinge besonders zu achten:

1. M, die Formelsprache von Power Query, ist case sensitive, d. h. es ist für das Funktionieren der Formeln von immenser Wichtigkeit, dass auf Groß- und Kleinschreibung geachtet wird. Wird dies missachtet, dann funktioniert die Formel schlicht nicht.
2. Die Funktion List.Numbers() verlangt als ersten Parameter den Startwert, ab dem die Liste gebildet werden soll und als zweiten Parameter die Anzahl an (in diesem Fall) Tagen, die vom Startwert aus weitergezählt werden soll. In unserem Fall also die Differenz aus Endedatum und Startdatum, ergänzt um einen zusätzlichen Tag.

      Abbildung 5 zeigt, wie dies in Power Query final aussieht.

Abbildung 5: Kalkulation der Liste, basierend auf Start- und Endedatum

      Das daraus resultierende Ergebnis mag zu Beginn etwas verwirren. Wie in Abbildung 6 ersichtlich, liefert die neu berechnete Spalte in jedem Datensatz den Wert List. 

Abbildung 6: Die zusammengeklappte Liste

      Was Power Query intern damit meint, habe ich in Abbildung 7 dargestellt.

Abbildung 7: Wie die Liste intern funktioniert

      Sobald der in Abbildung 6 rot markierte Knopf betätigt wird, wird die Liste aufgeklappt, was für das Beispiel Peter Mustermann folgendes bedeutet:

     Für die Werte 42118, 42119, bis 42130 wird ein neuer Datensatz erzeugt, während die Informationen zu Projektleiter und Projektbezeichnung dupliziert werden. Das Ergebnis der aufgeklappten Liste seht ihr in Abbildung 8.

Abbildung 8: Das Ergebnis der aufgeklappten Liste

    Jetzt geht es nur noch darum überflüssige Spalten zu entfernen und Datentypen zu ändern. Da der Projektstart und das Projektende nun nicht mehr als separate Spalten notwendig sind, werden diese (wie in Abbildung 9 beschrieben) entfernt.

Abbildung 9: Das Entfernen überflüssiger Spalten

     Im Anschluss sollte in der Spalte Datum der Datentyp von Ganzzahl in Datum geändert werden, damit auch wirklich ein Datum erkenntlich ist (siehe Abbildung 10). 

Abbildung 10: Änderung des Datentyps der Datumsspalte

    Damit die Datumsspalte auch an erster Stelle der Tabelle steht, kann mit einem Rechtsklick auf die Tabelle das Verschieben an den Anfang ausgewählt werden (siehe Abbildung 11).

Abbildung 11: Verschieben der Datumsspalte an den Anfang der Tabelle

     Hiermit ist die Abfrage fertiggestellt, so dass wir das Rückgabeergebnis in Excel sehen wollen. Dies geschieht mit einem Klick auf Schließen & laden, wie in Abbildung 12 beschrieben.

Abbildung 12: Speichern der erstellten Power Query Abfrage

     Daraufhin wird das Ergebnis der Abfrage in eine neue Excel-Tabelle geladen, wie in Abbildung 13 dargestellt. 

Abbildung 13: Das fertige Ergebnis in Excel

     Das Schöne an dieser Abfrage ist nicht nur, dass die gewünschte Transformation schnell und relativ leicht zu bewältigen war, sondern auch, dass diese Abfrage eine hohe Nachhaltigkeit mit sich bringt. Sofern in den Basisdaten Veränderungen stattgefunden haben, beispielsweise durch neue Datensätze, kann mit einem einfachen Rechtsklick auf das Abfrageergebnis in Excel die Aktualisierung der Abfrage vorgenommen werden (siehe Abbildung 14). Wenige Sekunden, nachdem sich die Basisdaten geändert haben, ist die gesamte Transformation also erneut ausgeführt. Ist das nicht genial?

Abbildung 14: Die Abfrage aktualisieren

     Für mich steht eines fest: Sofern es in Zukunft um das Transformieren von Daten geht, werde ich es immer zuerst mit Power Query zu lösen versuchen. Zudem wird meine innere Einstellung bzgl. der Frage, ob eine bestimmte Transformation mit Power Query gehen kann, lauten: Ja, das kriegen wir mit Power Query hin J