Is there a simple way to convert a timestamp in UTC to your local time zone in Power Query? Yes and no. Let’s find out what the options are.
TLDR section
https://github.com/nolockcz/PowerQuery/tree/master/UTC%20to%20Local%20Time However, I personally recommend reading the article once before you use it in your project.
Research
I have started with research on the internet. Maybe there is a solution I can use without spending time on writing my own code. Let’s list some of them — I do not want to call them solutions, a finding is a better word, I think.
Finding №1
On forums, you can find a recommendation for using the function DateTime.AddZone. Well, this function just adds an offset to a datetime value. If you live in a country without daylight saving time (DST), it can be your choice.
An example which shifts the datetime by +01:00: DateTime.AddZone(#datetime(2020, 3, 9, 10, 11, 12), 1, 0). The result is of data type datetimezone.
Finding №2
Or another recommendation is using a duration like #datetime(2020, 3, 9, 10, 11, 12) + #duration(0, 1, 0, 0) which does almost the same as the previous example. The result is of the data type datetime.
Finding №3
The most original solution in my opinion is this one (source):
// Convert "Created" date from UTC to AEST
let createdString = Number.ToText(Date.Year([Created]))
& Text.PadStart(Number.ToText(Date.Month([Created])), 2, "0")
& Text.PadStart(Number.ToText(Date.Day([Created])), 2, "0")
& Text.PadStart(Number.ToText(Time.Hour([Created])), 2, "0"),createdNum = Number.FromText(createdString),// AEST time: get the offset depending on whether it's daylight savings time or not (GMT+11 or GMT+10) by looking at the DST change-over times in GMT 0
timeZoneOffsetAEST = if createdNum >= 2017093016 and createdNum < 2018033117
then "11"
else
if createdNum >= 2018100616 and createdNum < 2019040617
then "11"
else
if createdNum >= 2019100516 and createdNum < 2020040417
then "11"
else
if createdNum >= 2020100316 and createdNum < 2021040317
then "11"
else "10",createdInAEST = DateTimeZone.SwitchZone(DateTimeZone.From([Created]),Number.FromText(timeZoneOffsetAEST),0)in createdInAEST
It is a time bomb! It works for timestamps between March 2017 and September 2021 in Australia. Before and after that the conversion will be wrong! But there is at least an elementary support for DST
Finding №4
There is another M function, which you can use for the conversion: DateTimeZone.ToLocal. It works in Power BI Desktop because it takes the offset of the time zone from your operating system. But what happens in Power BI Service? The Azure cloud runs using UTC only. It means, a UTC timestamp stays unchanged if you refresh your data in the cloud.
Finding №5
Chris Webb wrote an article about DST in Power Query where he says: “Frustratingly there doesn’t seem to be a way to convert a UTC time to the correct local time in any given time zone in M — the DateTimeZone.SwitchZone() function just adds/removes hours without taking Daylight Saving Time into account, so it’s not really very useful.”
A solution
If the good uncle Google does not help, I have to write my own solution. It handles DST and it should work in all countries where the switch between standard and daylight saving time is regular. If you want to know more about different rules around the globe, visit http://www.webexhibits.org/daylightsaving/g.html or https://en.wikipedia.org/wiki/Daylight_saving_time_by_country or another similar website.
The function, I have written, has one parameter of the type any — I want to have just one function which accepts a datetime or a datetimezone timestamp. Later in the function body there is a check if it is one of these datatypes.
(UTCTimestamp as any) as nullable datetime =>At the beginning, there is a record which defines what your offset during standard and DST is. You should change these offsets if you do not live in +01:00 / +02:00 DST. The record contains also a function called fnDaylightSavingTimePeriod, which calculates the start and the end of DST in your time zone. I have implemented the logic used in the European Union (last Sunday of March, last Sunday of October). If you have another rule set than mine, you have to modify this function too. (Why so complicated with the record? I usually have a record containing a global configuration for a dataset. And the offsets and the function fnDaylightSavingTime are a part of it.)
/*
* The record defines when the daylight saving time period starts and ends
* and what the standard and daylight saving time offsets are.
*/
TimeZoneConfiguration = [
// standard offset
StandardOffset = #duration(0, 1, 0, 0),
// the day light saving time offset
DaylightSavingTimeOffset = #duration(0, 2, 0, 0),
/*
* Get start and end of daylight saving time.
* This code implements the rules of EU counties. If it doesn't fill your expectations,
* go to https://en.wikipedia.org/wiki/Daylight_saving_time_by_country and modify the code below.
*/
fnDaylightSavingTimePeriod = (
timestamp as datetime
) as record =>
let
// the daylight saving time starts on the last Sunday of March at 1am UTC
LastDayOfMarch = #date(Date.Year(timestamp), 3, 31),
StartOfDaylightSavingTime = Date.AddDays(LastDayOfMarch, -Date.DayOfWeek(LastDayOfMarch, Day.Sunday)) & #time(1, 0, 0),
// the daylight saving time ends on the last Sunday of October at 1am UTC
LastDayOfOctober = #date(Date.Year(timestamp), 10, 31),
EndOfDaylightSavingTime = Date.AddDays(LastDayOfOctober, -Date.DayOfWeek(LastDayOfOctober, Day.Sunday)) & #time(1, 0, 0),
Result =
[
From = StartOfDaylightSavingTime,
To = EndOfDaylightSavingTime
]
in
Result
],The rest of the code is very simple. It checks if your timestamp is in the standard or DST period and add the offset to the timestamp.
// convert UTC to local time defined by an offset
LocalTime =
if DateTimeUTC = null then
null
else if DateTimeUTC >= DaylightSavingTimePeriod[From] and DateTimeUTC < DaylightSavingTimePeriod[To] then
DateTimeUTC + TimeZoneConfiguration[DaylightSavingTimeOffset]
else
DateTimeUTC + TimeZoneConfiguration[StandardOffset]And the whole function (also available on GitHub):
(UTCTimestamp as any) as nullable datetime =>
let
/*
* The record defines when the daylight saving time period starts and ends
* and what the standard and daylight saving time offsets are.
*/
TimeZoneConfiguration = [
// standard offset
StandardOffset = #duration(0, 1, 0, 0),
// the day light saving time offset
DaylightSavingTimeOffset = #duration(0, 2, 0, 0),
/*
* Get start and end of daylight saving time.
* This code implements the rules of EU counties. If it doesn't fill your expectations,
* go to https://en.wikipedia.org/wiki/Daylight_saving_time_by_country and modify the code below.
*/
fnDaylightSavingTimePeriod = (
timestamp as datetime
) as record =>
let
// the daylight saving time starts on the last Sunday of March at 1am UTC
LastDayOfMarch = #date(Date.Year(timestamp), 3, 31),
StartOfDaylightSavingTime = Date.AddDays(LastDayOfMarch, -Date.DayOfWeek(LastDayOfMarch, Day.Sunday)) & #time(1, 0, 0),
// the daylight saving time ends on the last Sunday of October at 1am UTC
LastDayOfOctober = #date(Date.Year(timestamp), 10, 31),
EndOfDaylightSavingTime = Date.AddDays(LastDayOfOctober, -Date.DayOfWeek(LastDayOfOctober, Day.Sunday)) & #time(1, 0, 0),
Result =
[
From = StartOfDaylightSavingTime,
To = EndOfDaylightSavingTime
]
in
Result
],
DateTimeUTC =
if Value.Is(UTCTimestamp, type nullable datetime) then
UTCTimestamp
else if Value.Is(UTCTimestamp, type nullable datetimezone) then
DateTimeZone.RemoveZone(UTCTimestamp)
else
error Error.Record(
"Invalid data type of parameter UTCTimestamp",
null,
"Allowed data types are 'datetime' and 'datetimezone'."
),
// get daylight saving time period
DaylightSavingTimePeriod = TimeZoneConfiguration[fnDaylightSavingTimePeriod](DateTimeUTC),
// convert UTC to local time defined by an offset
LocalTime =
if DateTimeUTC = null then
null
else if DateTimeUTC >= DaylightSavingTimePeriod[From] and DateTimeUTC < DaylightSavingTimePeriod[To] then
DateTimeUTC + TimeZoneConfiguration[DaylightSavingTimeOffset]
else
DateTimeUTC + TimeZoneConfiguration[StandardOffset]
in
LocalTimeNext steps
If you think, there is a rule in your country which you are unable to write in M, let me know down in the comments. I will do my best to help you.