Power Query im Überblick
Datum und Zeit in Power Query
Effizient mit Datum und Zeit arbeiten — Ein Überblick
English version of this article here.
Automatisiertes, formel-basiertes Verarbeiten von Datums- und Zeitangaben ist fast immer eine leidvolle und undankbare Angelegenheit. Mit cleveren Funktionen macht Power Query hieraus jedoch ein Kinderspiel, da an vielen Stellen keine Formeln mehr erforderlich werden— Zeit für einen Überblick.
1. Import internationaler Datumsangaben
a) Tausend und ein Datum-Format
Probleme mit Datumsangaben beginnen oftmals schon beim Import eines simplen Datums. Aus irgend einem Grund existieren auf dieser Welt scheinbar unendlich viele Schreibweisen und Formatierungen für Datumsangaben. So kann der 5. April 2001 allein hier in Deutschland als
- 5.4.2021 (D.MM.YYYY),
- 05.04.2021 (DD.MM.YYYY),
- 5.4.21 (D.M.YY) oder
- 05.04.21 (DD.MM.YY)
dargestellt werden.
In anderen Ländern und Sprachräumen wird das gleiche Datum auch als
- 04.05.2021 (MM.DD.YYYY),
- 04/05/2021 (MM/DD/YYYY) oder auch
- 05/04/2021 (DD/MM/YYYY)
zu Papier gebracht. ISO-8601 wartet dann auch noch mit der Variante
- 2021–04–05 (YYYY-MM-DD)
auf.
Beim Importieren von Datumsangaben ist es daher wichtig, dass all diese Formate als korrektes Datum erkannt werden. Als Beispiel soll uns folgende Musterdatei dienen.
In jeder Spalte ist das Datum in einer anderen Formatierung/Darstellung als Zeichenkette enthalten:
- Spalte 1: DD.MM.YYY
- Spalte 2: YYYY-MM-DD
- Spalte 3: D.M.YY
- Spalte 4: MM/DD/YYY.
b) Datum als Datum erkennen — eine Frage der Interpretation
Ein Datum als Zeichenkette nützt uns i.d.R. wenig, jedenfalls dann nicht, wenn wir mit dem Datum irgendwelche Berechnungen oder Auswertungen vornehmen wollen. Selbst ein einfacher Datumsfilter setzt ein echtes Datum voraus, keine Zeichenkette die lediglich aussieht wie ein Datum.
Im nächsten Schritt ändern wir daher bei allen Spalten den Datentyp zu “Datum” (“Date”).
Das Ergebnis sieht dann wie folgt aus.
Falls es bei euch anders aussieht, bitte weiterlesen, Erklärung folgt gleicht.
Das Problem in Zeile 1 ist offensichtlich, obwohl immer der 4. Dezember 2021 (deutsche Repräsentation: 04.12.2021) importiert werden sollte, haben wir in Spalte 1 und 3 als Datum den 12. April 2021 (deutsche Repräsentation: 12.04.2021) erhalten. Warum, weil Power Query die Zeichenkette in der Form MM.DD.YYYY erwartet jedoch in der Form DD.MM.YYYY bekommen und somit das Datum falsch interpretiert hat.
Solche Fehler können schwerwiegende Folgen haben und dennoch lange unerkannt bleiben oder zu scheinbar merkwürdigen Ergebnissen und Fehlern führen.
Auffälliger wird das Problem in der vierten Zeile. Hier konnte Power Query mit einem 22. Monat nichts anfangen und hat uns einen Fehler ausgeworfen.
Solche Fehler sind dankbarer, weil sie sofort zum “Crash” führen. Sie zeigen aber, wie nötig umfangreiche und sinnvolle Test bei Anwendungen mit internationalen Datumsangaben sind.
Wir sehen also, Power Query versucht sein Bestes, das Datum aus der ursprünglichen Zeichenkette in ein zutreffendes Datum umzuwandeln. Allerdings misslingt dieser Versuch, sobald die Zeichenkette nicht der Formatierung entspricht, die Power Query erwartet.
Doch was erwartet Power Query eigentlich? Und was ist die Lösung?
c) Einstellung des globalen Gebietsschemas
Was Power Query erwartet bzw. wie Power Query ein Datum aus einer Zeichenkette interpretiert, hängt von euren Regional-Einstellungen ab. Das Ergebnis des vorigen Schrittes könnte bei euch daher auch anders aussehen, z.B. so:
Nämlich dann, wenn ihr in den Optionen bei den Regional-Einstellungen ein anderes Gebietsschema einstellt.
Achtung! Wie das Datum bei euch angezeigt wird, ist nochmals wieder eine andere Frage, die von den Einstellungen eures Betriebssystems abhängig ist. Hier geht es ausschließlich um die Frage, wie ein Datum eingelesen und intern erkannt wird.
d) Es geht auch ganz einfach — Gebietsschema individuell wählen
Nun ist es wenig praktisch, ständig das globale Gebietsschema zu ändern (oder gar im Betriebssystem rumzufummeln). Manchmal ist es auch gar nicht möglich — und nötig ist es auch nicht, denn Power Query bietet eine einfache und individuelle Möglichkeit mit verschiedenen Datumsformaten umzugehen. Und dies geht so:
Bzw. in der deutschen Version hier:
Auf diese Weise können wir für jede Spalte und jede Abfrage individuell das Ursprungsformat (nicht das Zielformat, dieses hängt vom Betriebssystem ab!) auswählen.
Und das Ergebnis:
Für das englische Datum in Spalte 4 sei hier auch noch mal der Unterschied zwischen USA und UK gezeigt.
DD/MM/YYYY vs. MM/DD/YYYY
Auf diese Weise können wir also höchst unterschiedliche Datumsangaben zutreffend, einfach und sicher einlesen. Alle Fehler sind im unteren Bild verschwunden, alle Datumsangaben sind zutreffend eingelesen.
e) Gebietsschema per Formel wählen
Falls ihr mal keine Lust habt, mit der Maus das Gebietsschema auszuwählen, könnt ihr dieses auch einfach in die Formel eintragen.
Typische ISO-Codes sind hier:
- en-US: Englisch (USA)
- de-DE: Deutsch (Deutschland)
- en-GB: Englisch (UK)
f) Aus Beispielen ableiten
Falls ihr das passende Gebietsschema nicht findet oder das Datum in einer sehr ungewöhnlicher Form vorliegt, kann Power Query das richtige Datum auch Anhand von Beispielen “erraten”. Dies geht wie folgt.
Jetzt gebt ihr einfach für ein oder zwei Beispiele das richtige Ergebnis ein.
Mit etwas Glück, errät Power Query, für die verbliebenen Zeilen das Ergebnis (hellgrauer Text im Bild oben) und schlägt auch eine Formel vor.
Im vorliegenden Beispiel haben wir dann eine neue Spalte mit unserem Datum als Zeichenkette im deutschen Format. Dies können wir auf einem der oben beschriebenen Wege schließlich wieder leicht in ein echtes Datum umwandeln.
2. Arbeiten mit Datumsangaben
Der Import von Datumsangaben ist zumeist kein Selbstzweck. Oft wollen wir mit dem Datum arbeiten. Und wer sich schon mal in Excel mit Datums-Funktionen ala JAHR() , DATUM() , DATWERT() oder DATEDIF() beschäftigen musste, weiß, was für grauenhafte Formelorgien teilweise notwendig sind um einfachste Berechnungen anzustellen. Wesentlich eleganter geht es mit Power Query.
Insbesondere für abgeleitete Informationen, bietet Power Query hierzu gleich an zwei Stellen viele nützliche (im Wesentlichen gleiche) Funktionalitäten. Warum an zwei Stellen die gleichen Funktionalitäten? Nun, einmal unter “Transform” (“Transformieren”).
In diesem Fall wird die aktuelle Datumsspalte ersetzt.
Und einmal unter “Add Column” (“Spalte hinzufügen”). In diesem Fall wird eine Spalte mit der Berechnung hinzugefügt.
a) Informationen zum Jahr
Angenommen, wir brauchen in unserem Datenmodell nur das Jahr? Kein Problem, ein Klick genügt.
Fertig!
Power Query hat uns eine Spalte hinzugefügt, die in der alten Excel-Welt der Funktion JAHR() entsprochen hätte.
Auf ähnlich einfache Weise können wir uns den Jahresanfang und das Jahresende ausgeben lassen. Dies kann eine nützliche Funktion sein, wenn wir Fristenberechnungen anstellen müssen, die sich auf das Ende oder den Anfang des Jahres beziehen.
b) Differenz berechnen
Wäre es nicht interessant zu wissen wie viele Tage zwischen dem Datum und dem Jahresende liegen. Kein Problem: Wir markieren einfach beide Spalten (STRG + Mausklick).
Mit einem weiteren Klick können wir die Tagesdifferenz ermitteln lassen.
Und ohne auch nur eine Formel zu schreiben, erhalten wir eine Spalte mit der Differenz in Tagen.
c) Monate analysieren
Was mit Jahren geht, geht auch mit Monaten. Wir können aus einem Datum mit wenigen Klicks Informationen zum Monat extrahieren.
So können wir uns den Monat als Zahl, den Monatsanfang und das Monatsende, die Anzahl der Tage des Monats und den Namen des Monats ausgeben lassen.
Wie man an den letzten beiden Zeilen erkennt, beachtet Power Query dabei auch Schaltjahre: 29. Februar 1984 vs. 28. Februar 1986.
d) Quartale
Ein ziemlich cooles Feature sind die Funktionen zu Quartalen. Gerade für Finanzberichterstattung werden diese Informationen und Einordnungen ständig benötigt.
Das Ergebnis spricht für sich.
e) Wochen
Was für Quartale geht, geht auch für Informationen zur Woche.
Und liefert das folgende Ergebnis.
f) Tage
Unter dem Punkt “Tag” (“Day”) finden sich weitere selbsterklärende aber dennoch interessante Punkte, die insbesondere bei der Analyse von Finanzdaten hilfreich sein können.
Die Punkte “Start of Day” (“Tagesbeginn”) und “End of Day” (“Tagesende”) sind für reine Datumsformate uninteressant, für Date/Time Formate können Sie jedoch durchaus hilfreich sein, wie im unteren Bild dargestellt.
g) Nur Datum
Wo wir nun schon einmal mit Date/Time herumoperieren, bleiben wir kurz dabei. Gelegentlich kommt es vor, dass wir aus Datenbanken Date/Time Spalten erhalten, weil die Datenbank automatisch Zeitstempel speichert. Falls uns nur das Datum interessiert, brauchen wir hier keine wilden Zeichenkettenoperationen vornehmen, sondern teilen Power Query unseren Wunsch per Klick mit.
Aus Date/Time wird Date.
Alternativ hätten wir auch einfach den Datentyp auf Datum umstellen können.
h) Alter ermitteln
Der Vollständigkeit halber sei auch noch die Ermittlung des Alters mit Power Query hier vorgestellt. Nehmen wir an, wir haben eine Spalte mit Geburtsdaten (hier im Format DD.MM.YYYY).
Angenommen, für irgendwelche Zwecke benötigen wir in unserem Datenmodell das Alter der Person. Nichts leichter als das.
Schon haben wir folgendes Ergebnis.
In der Spalte “Age” erhalten wir das Alter in Tagen. Dieses können wir ohne zu rechnen wie folgt in Jahre umwandeln.
Das Ergebnis sieht dann wie folgt aus.
In der Praxis benutzen wir eher selten Dezimal-Jahre bzw. ein Dezimal-Alter. Stattdessen runden wir in der Regel bis zum nächsten Geburtstag ab. Auch hierfür bedarf es in Power Query keiner Formel, es genügt ein Klick.
Ob die Ermittlung des Alters in Jahren aus den Tagen in jedem Fall korrekt ist, weiß ich nicht. Im Zusammenhang mit Schaltjahren und Personen, die an 29. Februar geboren wurden, habe ich Zweifel, wenn die Ermittlung um dieses Datum herum vorgenommen wird. Vor Implementierung in wichtigen Modellen empfehle ich dringend eigene Tests anzustellen.
3. Arbeiten mit Zeit
a) Allgemein
Aus einem “Date/Time”-Datum können wir — ähnlich wie oben mit der Datum Schaltfläche — mit der “Zeit”-Schaltfläche diverse Bestandteile extrahieren. Vgl. Abbildung unten.
b) Reine Zeitangaben
In Power Query können auch reine Zeitangaben verarbeitet werden, losgelöst von einem Datum. Zu beachten ist, dass es sich hierbei um Uhrzeiten handelt, nicht um Zeiten im Sinne einer Dauer (z.B. Betriebsstunden, Arbeitsstunden etc.). D.h. insbesondere ein Aggregieren ist hier nicht (ohne weiteres) möglich.
c) Zeitzone
Power Query unterstützt auch Datums- und Zeitangaben im Zusammenhang mit Zeitzonen.
Dies sieht dann wie folgt aus.
Aus derartigen Datumsangaben können wir ohne weiteres auch eine Lokalzeit bestimmen.
4. Arbeiten mit Zeitspannen (Dauer)
a) Allgemeines
Mit dem Datentyp “Dauer” können wir Zeitspannen in Power Query verarbeiten.
Die Zeitspanne wird dann wie folgt dargestellt.
Tag.Stunden:Minuten:Sekunden
Für Zeitspannen gibt es unter der Schaltfläche “Dauer” (“Duration”) wiederum jede Menge kleine Helferfunktionen.
b) Rechnen
Mit dem Datenformat “Dauer” können wir recht einfach Berechnungen vornehmen. Im unteren Beispiel addieren wir drei Spalten zusammen.
Als Ergebnis erhalten wir eine Spalte mit den Berechnungsergebnissen.
In einfachen Fällen geht es übrigens auch ohne Formeln. Hierzu wählen wir die Spalten aus, mit denen wir Berechnungen vornehmen wollen. Dies geht durch drücken der STRG-Taste bzw. SHIFT-Taste. Anschließend wählen wir über die Schaltfläche “Dauer” (“Duration”) die gewünschte Berechnung aus.
Auch auf diese Weise erhalten wir eine Summenspalte.
Zur Umwandlung dieser Ergebnisse später mehr.
c) Aggregieren
Wir können den Datentyp “Dauer” auch im Rahmen von Aggregationen nutzen. Nehmen wir das nachfolgende Beispiel.
Angenommen wir wollen die Betriebsstunden je Roboter aggregieren.
Mit Hilfe des üblichen Aggregationsmenüs erhalten wir das folgende Ergebnis.
d) Converting Duration
Bleiben wir kurz noch bei den vorigen Beispielen. Gelegentlich kann es vorkommen, dass wir z.B. Betriebsstunden nicht in Tagen angezeigt haben wollen. Hier hilft uns wieder die Schaltfläche “Dauer” (“Duration”).
Auf diese Weise können wir die Betriebszeit z.B. in Dezimal-Stunden, Dezimal-Minuten oder Dezimal-Sekunden umwandeln.
Im Zusammenhang mit Datum/Zeit in Power Query gibt es noch eine Menge mehr zu schreiben. Insbesondere zu komplexeren Fällen. Als Intro sollen die gezeigten Punkte jedoch reichen.
