This article explains how to convert from UTC to Local time with the Power Query M Language. It sounds easy but it’s not so trivial.
Power Query M Language is supporting a
datetimezone type and expose a function
DateTimeZone.ToLocal. These artefacts are doing the job and you can quickly create the following function, converting a UTC datetime to a local datetime:
let UtcToLocal = (utc as datetime) => let utcToLocal = DateTimeZone.RemoveZone( DateTimeZone.ToLocal( DateTime.AddZone(utc,0) ) ) in utcToLocal in UtcToLocal
I’m from Belgium (UTC+1 during winter time and UTC+2 during summer time) and the following conversions are just fine:
UtcToLocal(#datetime(2018, 3, 21, 17, 0, 0)) = #datetime(2018, 3, 21, 18, 0, 0) #Winter time => +1 UtcToLocal(#datetime(2018, 3, 30, 17, 0, 0)) = #datetime(2018, 3, 21, 19, 0, 0) #Summer time => +2
When I try to convert the datetime around the switch from summer time to winter time, I’ve twice the local time 02:00:00, once at 00:00:00UTC and once at 01:00:00UTC. That’s indeed the case.
UtcToLocal(#datetime(2017, 10, 29, 0, 0, 0)) = #datetime(2017, 10, 29, 2, 0, 0) #Summer time => +2 UtcToLocal(#datetime(2017, 10, 29, 1, 0, 0)) = #datetime(2017, 10, 29, 2, 0, 0) #Winter time => +1
Everything is fine … except if I share my code with someone from another time zone. The function
DateTimeZone.ToLocal is relying on regional settings and in that case my conversion should always be from UTC to “Brussels time”.
I didn’t find any other way to ensure that I’m always converting from UTC to “Brussels time” than implementing the conversion by myself. That’s the goal of the following function:
UtcToLocalTime = (date as date, time as time) =>
In Europe, the two pivotal daylight savings dates are the last Sundays of March and October. Translated in M language, it means the first day of the week, assuming the week is starting a Sunday, containing the last day of this month (which is a 31).
lastSundayOfOctober = Date.StartOfWeek(#date(Date.Year(date), 10, 31), Day.Sunday), lastSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 31), Day.Sunday),
Based on the two pivotal dates, I can check if I’m in winter time or summer time
isSummerTime = (date > lastSundayOfMarch and date < lastSundayOfOctober) or (date = lastSundayOfOctober and time #time(1,0,0)),
And based on that adjust the time zone
timeZone = 1 + Number.From(isSummerTime), localTime = DateTime.From(date) + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time)) + #duration(0, timeZone, 0, 0),
I can also check if the specified date and time corresponds to the doubled-hour of the last Sunday of October and return a symbol to that date (in a text format)
isWinterDouble = (date = lastSundayOfOctober) and time = #time(1,0,0), localString = DateTime.ToText(localTime, "yyyy-MM-dd HH:mm:ss") & Text.Repeat("*", Number.From(isWinterDouble)),
The final result is a record with the local date, time and textual representation:
record = [ LocalDate = Date.From(localTime), LocalTime = Time.From(localTime), LocalDateTime = localTime, LocalString = localString ] in record;
This function is returning the expected result: