Quite a few people (Ken Puls, Reza Rad and today Chris Koester) have blogged about how the M date/time zone functions work in Power Query/Power BI, and the related problem of turning a UTC time into the actual time in any given location taking Daylight Saving Time changes into account. I don’t have much to add to what they have written, but I did learn something new about this subject from a customer last week: it is possible to do the conversion in pure M if the time zone you’re converting to is your PC’s own local time zone.
Here’s an example. In 2017 the clocks in the UK went forward at 1am on Sunday March 26th. Given the following table with two times in it, one of which is 1pm on Saturday March 25th and one of which is 1pm on Sunday March 26th:
…if you assume that these times are both UTC, here’s a query that returns the correct equivalent local time for me (because my PC is set to UK time) for both of them:
[sourcecode language=”text” padlinenumbers=”true”]
let
//Read data from Excel table
Source =
Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//Change the type of the column to datetime
ChangeType1 =
Table.TransformColumnTypes(
Source,
{{"Time", type datetime}}),
//Turn this into a UTC time
ConvertToUTC =
Table.AddColumn(
ChangeType1,
"UTC",
each DateTime.AddZone([Time],0)),
//Change the data type to datetimezone
ChangeType2 =
Table.TransformColumnTypes(
ConvertToUTC,
{{"UTC", type datetimezone}}),
//Convert to local PC time
ConvertToLocal =
Table.AddColumn(
ChangeType2,
"Local",
each DateTimeZone.ToLocal([UTC])),
//Change type to datetimezone
ChangeType3 =
Table.TransformColumnTypes(
ConvertToLocal ,
{{"Local", type datetimezone}})
in
ChangeType3
[/sourcecode]
Here’s a brief explanation of what the query does:
- First it reads the times from the Excel table and sets the Time column to be datetime data type
- It then creates a new column called UTC and then takes the values in the Time column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times
- Finally it creates a column called Local and converts the UTC times to my PC’s local time zone using the DateTimeZone.ToLocal() function
As you can see from the output table, the DateTimeZone.ToLocal() function has correctly handled the UK Daylight Saving Time change by converting the UTC time 13:00 on March 25th 2017 to 13:00 UK time, and converting the UTC time 13:00 on March 26th 2017 to 14:00 UK time.
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. Apart from all the blog posts linked to at the start of this post, when I posted this question on the Power Query forum I got a very helpful answer from Marcel Beug with a completely different approach to solving the problem here, although it’s still not a straightforward one. If anyone from Microsoft is reading, it would be great to have a new M function to handle this requirement!
It’s on our backlog.
Great, thanks!
Great news. Any updates on release timeline? I’m not finding it in ideas or forum, but could have easily missed it. Having just recently done the research on other’s approaches and not really liking them, I came up with my own as described here: https://intellitect.com/convert-uct-local-time-daylight-savings-support-power-bi/ .
Great post Chris! When using this with Power BI, would it evaluate once at the Gateway, at the Power BI server, or would it evaluate at the client (Assuming these were all at different Time zones?
If you deploy to the server and refresh your data, it’s going to get the server time which is UCT.
Hi Chris,
had you any update on this?
There haven’t been any changes as far as I know
Hi Chris,
I noticed it is a year from the last comment on this subject. I have been trying to resolve how best to handle Daylight Saving Hours but without any luck so far. My reports need to work on “service” and therefore when localtime is same as UTC.
I have managed to add a daylight savings indicator on the DAX built CalendarAuto() Dates table but really need this at the power query stage to enable me to build a model relationship between Dates table and the imported data(adjusted date).not the UCT date.
With UCT date/time of 31 March 2020 23:00, actually adjusting to 1 April 2020 00:00 hours it makes quite a difference. i.e dates not only wrong day but wrong month.
Nothing has changed since my last comment, unfortunately…
I have managed to come up with the following simply query to handle BST (Daylight Saving) in the UK.
This takes the date as a parameter, needs to be in Date/Time format, and checks to see if the date is between the last Sunday in March and last Sunday in October (UK Daylight Saving) for the year of the date provided. If it does then it adds 1 hour to the date and returns it to be used. I got the basis for this from other similar US versions I found.
let
AdjustedDate = (InDate as any) =>
let
OutDate = if InDate >= Date.StartOfWeek(#datetime(Date.Year(InDate),03,31,0,0,0),1) and
InDate <= Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),1)
then InDate + #duration(0,1,0,0)
else InDate
in
OutDate
in
AdjustedDate
I’ve just come across this problem in 2022 – there still doesn’t seem to be a built-in solution.
And I then found that Gordon Parmenter’s solution, while clever, is slightly incorrect and goes from Monday to Monday and not the Last Sunday of March to the last Sunday of October, and ignores the times the clocks officially change (1am GMT forward, 2am BST backward). So I came up with this which I’m pretty certain is correct:
let
BSTAdjustedDateTime = (_myDate as datetime) =>
let
_BSTCheckDate =
if _myDate >= Date.StartOfWeek(#datetime(Date.Year(_myDate),03,31,0,0,0),Day.Sunday) + #duration(0,1,0,0) and
_myDate < Date.StartOfWeek(#datetime(Date.Year(_myDate),10,31,0,0,0),Day.Sunday) + #duration(0,1,0,0)
then _myDate + #duration(0,1,0,0)
else _myDate
in
_BSTCheckDate
in
BSTAdjustedDateTime
Nice improvement on taking account of official change in the clocks. I Didn’t need that precision on my original solution as I had a date rather than time issue. i.e Midnight coming up as 23:00 the day before.
My solution was built in my first few months of Power Query/BI endeavours, and after some frustration at not finding anyone had done this before. Now almost 2 years on I have learned and forgotten a lot along the way but did fix the Monday issue once we got to October and I realised the error. Now use Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),0) rather than Date.StartOfWeek(#datetime(Date.Year(InDate),10,31,0,0,0),1).
Great stuff. The blogpost was a while ago, and I’m sure you have a solution for it. For others interested, the code I like using is:
[code]
let
UTC_DateTimeZone = DateTimeZone.UtcNow(),
UTC_Date = Date.From(UTC_DateTimeZone),
StartSummerTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 3, 31), Day.Sunday),
StartWinterTime = Date.StartOfWeek(#date(Date.Year(UTC_Date), 10, 31), Day.Sunday),
UTC_Offset = if UTC_Date >= StartSummerTime and UTC_Date < StartWinterTime then 2 else 1,
CET_Timezone = DateTimeZone.SwitchZone(UTC_DateTimeZone, UTC_Offset)
in
CET_Timezone
[/code]
If you like you can find an elaboration on my blog: https://gorilla.bi/power-query/last-refresh-datetime/
Enjoy!
Unfortunately this isn’t a 100% reliable solution: you have hard coded the dates when daylight savings starts and ends (it can vary from year to year and country to country, and some countries don’t always have daylight savings time), and it doesn’t account for historic variations in offsets.
This one is pretty stable for the Dutch daylight savings. It’s always the last sunday of march and of october. Is that different for other countries?
Yes, it is different for different countries unfortunately
Given that there is still no adequate solution in PowerQuery, a call to this very simple API can be quite helpful: http://worldtimeapi.org
Thanks for this suggestion. This is pretty simple to use in PowerQuery.
@Chirs have you heard about anything to handling this within cloud environment wo using APIs or custom functions ?