The views and opinions expressed in this blog are those solely of the author(s) and do not necessarily reflect Microsoft’s current policy, position, or branding. For official announcements and guidance on Dynamics 365 apps and services, please visit the Microsoft Dynamics 365 Blog.
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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.
Business Applications communities