Conquering Time Zones

A client has Power BI models connected to Dynamics Online. Dynamics stores all dates in UTC instead of keeping the time offset, such as 7/14/2020 1:21:29 AM +00:00. Naturally, the users want to see dates localized to the US Eastern Time zone. Easy, right? Use the Power Query ToLocal time transformation (in the Transform ribbon, expand Time, and then click To Local) to offset with the desired number of hours. But there are a few issues with this approach:

1. It will work fine in Power BI Desktop (assuming you are on the Eastern Time zone because Power BI will pick your computer settings), but it won’t work when you publish the dataset to powerbi.com. The reason for this is that Microsoft sets the time zone of Azure servers to UTC irrespective of the geo location of the Power BI data center. So, when Power BI Service refreshes the dataset, it will convert dates to its local time (UTC) and the net effect is that the date remains unchanged.
2. You can attempt using the M DateTimeZone.SwitchZone function but for some obscure reason, casting the column data type to Date after the zone change puts the date back to UTC.
3. The hour offset changes depending on when the country switches to Daylight Saving Time.

When the going gets though, the developer writes some code which in this case would be a Power Query ToTimeZone query function.

# open a Power Query blank query in Advanced Query mode and paste the code. Then rename the query to ToTimeZone
let
 Source = (UTCTime, TimeOffset) =>
let
 CurrentYear = Date.Year(DateTime.FixedLocalNow()),
 DST_Start = Date.StartOfWeek(#datetimezone(CurrentYear,3,14,0,0,0,TimeOffset,0),Day.Sunday) + #duration(0,2,0,0),
 DST_End = Date.StartOfWeek(#datetimezone(CurrentYear,11,7,0,0,0,TimeOffset+1,0),Day.Sunday) + #duration(0,2,0,0),
 OffsetTime = if UTCTime >= DST_Start and UTCTime < DST_End then TimeOffset + 1 else TimeOffset,
 #"Change Time" = if UTCTime=null then null else DateTimeZone.RemoveZone(UTCTime) + #duration(0, AdjustDST, 0, 0)
in
 #"Change Time"
in
 Source

The function takes two arguments: UTCTime and TimeOffset. For each row, the source query will pass the date as of datetimezone data type. The TimeOffset is the default time offset outside Daylight Saving Time. So, for US Eastern time zone, the time offset will be -5. Notice that I don’t specify types for the arguments because you may have empty (null) dates. If null is passed to UTCTime, you’ll get a runtime error. Instead, the last line checks if the incoming date is null. The function adjusts the time offset during Daylight Saving Time. Finally, it removes the time zone from the original date, so we get the date in a datetime format and then adds the offset. So, 7/14/2020 1:21:29 AM +00:00 will become 7/13/2020 9:21:29 PM.

Once the ToTimeZone function is ready, we can call it from the source query, like so:

let
 Source = OData.Feed("https://<tenant>.crm.dynamics.com/api/data/v9.1/appointments ", null, [Implementation="2.0"]),
 ToLocalTimeZone = Table.TransformColumns(Source, {{"createdon", each ToTimeZone(_, -5)}, {"scheduledstart", each ToTimeZone(_, -5)}}),
 #"Changed Type" = Table.TransformColumnTypes(ToLocalTimeZone,{{"createdon", type date}})
in
 #"Changed Type"

Notice that I use the TransformColumn transformation so that I can convert multiple date columns in one line and don’t end up with additional custom columns. In other words, I’m updating the date columns in place. Finally, I change the data type to date so that I can join to a Date dimension.

You can easily extend this code to handle times in multiple time zones, such as to report the time depending on the geo location of the user who entered the data. This will require storing the user time zone, such as in the Dynamics systemusers table.