UTC in lokale Sommer-/Winterzeit umwandeln mit Power Query

Immer wieder stolpere ich über das Thema, das in der Datenquelle ein Zeitstempel als UTC-Zeitstempel vorliegt und wir diese Zeitstempel im Power BI Bericht als lokalen Zeitstempel ausweisen möchten. Eine besondere Herausforderung ist hier die Sommer-/Winterzeit (UTC+2/+1), die datumsabhängig eine Verschiebung des Offsets zur Folge hat.

Doch werfen wir zuerst einen Blick auf die Regelung zur Zeitumstellung in Deutschland (Der mitteleuropäischen Sommerzeit).

Die mitteleuropäische Sommerzeit beginnt am letzten Sonntag im März um 2:00 Uhr MEZ, indem die Stundenzählung um eine Stunde von 2:00 Uhr auf 3:00 Uhr vorgestellt wird.
Sie endet jeweils am letzten Sonntag im Oktober um 3:00 Uhr MESZ, indem die Stundenzählung um eine Stunde von 3:00 Uhr auf 2:00 Uhr zurückgestellt wird.

Wikipedia: https://de.wikipedia.org/wiki/Sommerzeit

Also ermitteln wir zuerst mit Power Query das Umstellungsdatum von Winter- auf Sommerzeit und von Sommer- auf Winterzeit. In diesem Beispiel für das Jahr 2022. Durch die Nutzung der Power Query Funktion Date.StartOfWeek, mit dem letzten Tag des Monats und dem Parameter Day.Sunday, erhalten wir immer den letzten Sonntag des Monats.

Power Query Abfrage für Umstellungsdatum von Winter- auf Sommerzeit 2022
Power Query Abfrage für Umstellungsdatum von Winter- auf Sommerzeit 2022
Power Query Abfrage für Umstellungsdatum von Sommer- auf Winterzeit 2022
Power Query Abfrage für Umstellungsdatum von Sommer- auf Winterzeit 2022

Die Zeitverschiebung findet um 2 und 3 Uhr der lokaler Zeit statt, was mit der vorhandenen Zeitverschiebung von 1 und 2 Stunden immer der UTC Zeit von 1 Uhr entspricht. Diese Stunde fügen wir mit der #time Syntax unserem berechneten Daten hinzu.

Power Query Abfrage für Umstellungsdatum von Winter- auf Sommerzeit 2022 mit Uhrzeit
Power Query Abfrage für Umstellungsdatum von Winter- auf Sommerzeit 2022 mit Uhrzeit

Mit diesen Angaben können wir nun in Power Query das Offset der Zeitzone für das entsprechende Datum in 2022 bestimmen. Für den Vergleich der Daten müssen wir vom Datentyp DateTimeZone mit der Funktion Date.TimeZone.RemoveZone die Zeitzoneninformation entfernen.

Power Query Abfrage zur Bestimmung des Offsets
Power Query Abfrage zur Bestimmung des Offsets

Mit der Funktion DateTimeZone.SwitchZone können wir dann das Offset auf den UTC-Zeitstempel anwenden und erhalten somit das Ergebnis in unserer lokalen Zeitzone.

Power Query Abfrage zum ändern der Zeitzoneninformationen
Power Query Abfrage zum ändern der Zeitzoneninformationen

Für eine einfache Wiederverwendung habe ich die oben beschriebenen Schritte in eine benutzerdefinierte Funktion zusammengefasst, die du für diesen Zweck verwenden kannst.

(DateTimeZoneUTC as datetimezone) as datetimezone =>
let
    Jahr = Date.Year(DateTimeZoneUTC),
    //Bestimmung Start der Sommerzeit
    Sommerzeit = Date.StartOfWeek( #date(Jahr, 03, 31), Day.Sunday) & #time(1,0,0),
    //Bestimmung Start der Winterzeit
    Winterzeit = Date.StartOfWeek( #date(Jahr, 10, 31), Day.Sunday) & #time(1,0,0),
    //Berechnung des Offset
    Offset = if DateTimeZone.RemoveZone(DateTimeZoneUTC) >= Sommerzeit and DateTimeZone.RemoveZone(DateTimeZoneUTC) < Winterzeit then 2 else 1,
    //Änderung der Zeitzoneninformation
    DateTimeZoneMEZ = DateTimeZone.SwitchZone(DateTimeZoneUTC,Offset)
in
    DateTimeZoneMEZ

Ein Anwendungsbeispiel könnte diese Abfrage sein, die den letzten Aktualisierungszeitpunkt der Abfrage ausgibt. Dieses kann als Anhaltspunkt für die letzte Aktualisierung des DataSets genommen und im Power BI Bericht angezeigt werden. Da die Zeitzone im Power BI Service UTC ist, sollte der Wert für die einfachere Lesbarkeit auf die Zeitzone des Berichtsbetrachters geändert werden. Im Fall dieses Blogartikels die mitteleuropäische Zeitzone.

let
    Quelle = Table.FromRows({{DateTimeZoneUTCtoMEZ(DateTimeZone.UtcNow())}},{"Zeitstempel"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Zeitstempel", type datetimezone}})
in
    #"Geänderter Typ"

Bei der Recherche zu diesem Blogpost habe ich viele ähnliche Beiträge gefunden, von denen ich ein paar hier verlinken möchte.

Veröffentlicht von

Marcus Wegener

Marcus Wegener

Marcus Wegener ist Anwendungsentwickler für Business Intelligence und erstellt Lösungen, mit denen sich große Datenmengen schnell analysieren lassen. Kunden nutzen seine Lösungen, um die Vergangenheit zu analysieren, die Gegenwart zu steuern und die Zukunft zu planen, um damit mehr Erfolg zu generieren. Dabei ist seine einzigartige Kombination aus Wissen und Auffassungsgabe ein Garant für ihren Erfolg.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.