How-To: Convert UTC to Your Local Time Zone in Power Query

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):

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.

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 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.

And the whole function (also available on GitHub):

Next 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.

Cloud BI Professional Architect