Custom Power BI UTC to Local Date/Time with Daylight Saving Time Function

Colin Maitland, 03 December 2019

In a previous article, I demonstrated several approaches for transforming Dynamics 365 UTC Date/Time values to New Zealand (NZ) Local Date/Time and Date values. In this article I demonstrate the structure of the custom Power BI function referred to in that article. This article demonstrates the internal structure of a custom Power BI function that contains mostly logic rather than a sequence of applied steps.

The function is named DateTimeZoneUTCToDateTimeNZLT. I have adapted this from one provided by Marcel Beug’s DateTimeUTCToLocalWithDST function. My version simplifies the input parameters from eleven to one and includes logic to handle null Date/Time/Zone values. I have also used different internal naming conventions. This function can be easily adapted to transform a Dynamics 365 UTC Date/Time value to another Time Zone and can be renamed to a name of your choice.

This image shows the function interface when used directly within the Power Query Editor. It takes a single UTC Date/Time/Zone parameter; e.g. a UTC Date/Time from Dynamics 365.

This image shows how the function is used within a Transform Columns step in a Power Query. The underscore within the function brackets on the third line represents the current value from the source column; i.e. the createdon column.

This image shows the internal structure of the function. The yellow highlight shows where I have removed several blocks of code so that the structure is more clearly seen. The DateTimeZoneUTCToDateTimeNZLT function encapsulates a FunctionType definition at 1.0, and two internal functions at 2.0, i.e. UTCDateTimeZoneToLocalDateTimeNotNull; and 3.0, i.e. UTCDateTimeZoneToLocalDateTime.

When invoked in Power Query the function is executed from the outer let statement to the inner let statements. There are two inner let statements.

Within the outer let statement, the block of code at 4.0 is executed first. This invokes the UTCDateTimeZoneToLocalDateTime function. When doing so, it applies the FunctionType configuration defined in 1.0.

The block of code at 3.0 is executed next; i.e. the UTCDateTimeZoneToLocalDateTime function which determines if the Date/Time/Zone value is null and either returns null or invokes the internal function named UTCDateTimeZOneToLocalDateTimeNotNull.

The block of code at 2.0 is conditionally executed last; i.e. the UTCDateTimeZOneToLocalDateTimeNotNull function which does the actual work of transforming a non-null UTC Date/Time/Zone value to NZ Local Date/Time value.

The returned value from the custom DateTimeZoneUTCToDateTimeNZLT function is then returned as transformed Date/Time value. A subsequent Power Query step may then be used to transform this to a Date value.

The following images show the steps that were not displayed in the previous image. These steps set variables and perform calculations and comparisons.

It is important to be aware that this logic does not accommodate changes to New Zealand Daylight Saving Time rules prior the last Sunday of September 2017. Since the start of Daylight-Saving Time in New Zealand these rules have changed nine times; i.e. in 1927, 1928, 1929, 1934, 1941, 1946, 1975, 1989 and 2007.

Firstly, there is logic for configuring the Month, Day, Week and Time when Local Daylight Saving starts and ends and the offsets relative to UTC for Local Daylight-Saving Time and Local Standard Time. You can configure these variables to be specific to a different Time Zone, or you can alter the function to include these as parameters.

Secondly, there is logic that calculates the actual Local DST start and end values relative to the UTC Date/Time value being processed.

Finally, there is logic that calculates the UTC equivalent of the Local DST start and end values relative to the UTC Date/Time value being processed. These are then used to determine if the UTC Date/Time value falls within the Local DST period.

In conclusion, this custom function solves the issue discussed in my previous article and demonstrates the structure of a custom function in Power BI where the function consists mostly of logic. This function is easily adapted to another Local Time Zone by changing the code at 2.2.2 and 2.2.3 as described in this article.