Power Query M Function - Nested - UTCDateTime to LocalDateTime

[colinmaitland]

Hi there

I have a Power BI data model that connects to Dynamics 365 (Online). I have added a custom M function in Power BI Desktop Power Query Editor that converts UTC Date/Time/Zone values to Local Date/Time values, in my case to NZ Local Date/Time values. This function handles Daylight Saving. I need the function to work for null and non-null UTC Date/Time/Zone values. The function works fine for null and non-null values when I invoke it directly.

However, as soon as I add it to a Power Query I the following error is displayed.

Here is the function I am using. Note: I have abbreviated the steps inside the let step at 2.2 to aid understanding of the structure. This abbreviated function, that does nothing more than remove the timezone, has the same issue. Essentially, I am trying to implement an IF THEN ELSE structure, where the ELSE step contains multiple lines, to handle null and not-null UTC Date/Time values.

let
    // 1.0 Define Function Type for Custom UTC DateTime To Local DateTime Function
    FunctionType = Type.ForFunction (
        [
            ReturnType = type nullable datetime,
            Parameters = [ UTCDateTime = type nullable datetimezone meta [ Documentation.FieldCaption = "UTC Date/Time" ] ]
        ],
    1 ),

    // 2.0 Custom UTC Date/Time to Local Date/Time Function
    UTCDateTimeToLocalDateTime = ( UTCDateTime as nullable datetimezone ) as nullable datetime =>
    let
        LocalDateTime = if ( UTCDateTime = null)
            then
                // 2.1 Return NULL
                null
            else
                // 2.2 Convert UTC Date/Time to Local Date/Time
                let
                    UTCDateTime = DateTimeZone.RemoveZone( UTCDateTime ),
                    LocalDateTime2 = UTCDateTime
                in
                    LocalDateTime2
    in
        LocalDateTime,

    // 3.0 Invoke Custom UTC Date/Time to Local Date/Time Function
    #"UTC Date/Time To Local Date/Time" = Value.ReplaceType( UTCDateTimeToLocalDateTime, FunctionType )
in
    #"UTC Date/Time To Local Date/Time"

Thanks for you support in advance

Colin Maitland

[colinmaitland · Accepted solution]

Hi there

After some more work on this, I have resolved the issue by renaming the variable used as the function parameter. Note: I have abbreviated the steps inside the let step at 2.2 to aid understanding of the structure.

let
    // 1.0 Define Function Type for Custom UTC DateTime To Local DateTime Function
    FunctionType = Type.ForFunction (
        [
            ReturnType = type nullable datetime,
            Parameters = [ UTCDateTimeZone = type nullable datetimezone meta [ Documentation.FieldCaption = "UTC Date/Time/Zone", Documentation.SampleValues = { "21/03/2012 12:00:00 AM +00:00" } ] ]
        ],
    1 ),

    // 2.0 Custom UTC Date/Time to Local Date/Time Function
    UTCDateTimeZoneToLocalDateTime = ( UTCDateTimeZone as nullable datetimezone ) as nullable datetime =>
    let
        LocalDateTime = if ( UTCDateTimeZone = null)
            then // 2.1 Return NULL
                null 
            else 
                let // 2.2 Convert UTC Date/Time/Zone to Local Date/Time
                    UTCDateTime = DateTimeZone.RemoveZone ( UTCDateTimeZone ),
                    LocalDateTime = UTCDateTime
                in
                    LocalDateTime
    in
        LocalDateTime,

    // 3.0 Invoke Custom UTC Date/Time to Local Date/Time Function
    #"UTC Date/Time To Local Date/Time" = Value.ReplaceType( UTCDateTimeZoneToLocalDateTime, FunctionType )
in
    #"UTC Date/Time To Local Date/Time"

Here is a second working version that uses a different structure. Note: I have abbreviated the steps inside the let step at 2.0 to aid understanding of the structure.

let
    // 1.0 Define Function Type for Custom UTC DateTime To Local DateTime Function
    FunctionType = Type.ForFunction (
        [
            ReturnType = type nullable datetime,
            Parameters = [ UTCDateTimeZone = type nullable datetimezone meta [ Documentation.FieldCaption = "UTC Date/Time/Zone", Documentation.SampleValues = { "21/03/2012 12:00:00 AM +00:00" } ] ]
        ],
    1 ),

    // 2.0 Custom UTC Date/Time to Local Date/Time Function - Non Null UTC Date/Time
    UTCDateTimeZoneToLocalDateTimeNotNull = ( UTCDateTimeZone as datetimezone ) as datetime =>
        let
            UTCDateTime = DateTimeZone.RemoveZone ( UTCDateTimeZone ),
            LocalDateTime = UTCDateTime
        in
            LocalDateTime,

    // 3.0 Custom UTC Date/Time to Local Date/Time Function
    UTCDateTimeZoneToLocalDateTime = ( UTCDateTimeZone as nullable datetimezone ) as nullable datetime =>
    let
        LocalDateTime = if ( UTCDateTimeZone = null)
            then null // 3.1 Return NULL
            else UTCDateTimeZoneToLocalDateTimeNotNull ( UTCDateTimeZone ) // 3.2 Convert UTC Date/Time/Zone to Local Date/Time
    in
        LocalDateTime,

    // 4.0 Invoke Custom UTC Date/Time to Local Date/Time Function
    #"UTC Date/Time To Local Date/Time" = Value.ReplaceType( UTCDateTimeZoneToLocalDateTime, FunctionType )
in
    #"UTC Date/Time To Local Date/Time"

Here is the full working version of my UTCDateTimeToNZLT function:

let
    // 1.0 Define Function Type for Custom UTC DateTime To Local DateTime Function
    FunctionType = Type.ForFunction (
        [
            ReturnType = type nullable datetime,
            Parameters = [ UTCDateTimeZone = type nullable datetimezone meta [ Documentation.FieldCaption = "UTC Date/Time/Zone", Documentation.SampleValues = { "21/03/2012 12:00:00 AM +00:00" } ] ]
        ],
    1 ),

    // 2.0 Custom UTC Date/Time to Local Date/Time Function - Non Null UTC Date/Time
    UTCDateTimeZoneToLocalDateTimeNotNull = ( UTCDateTimeZone as datetimezone ) as datetime =>
        let
            // 2.2.1 Remove Time Zone
            UTCDateTime = DateTimeZone.RemoveZone( DateTimeZone.ToUtc ( UTCDateTimeZone ) ),

            // START - Configure Local Time Zone Specific Parameters
            // 2.2.2 Define Local Daylight Saving Start Date and Time and Offset Paramaters
            startMonthNumber = 9 as number,
            startDayOfWeekNumber = Day.Sunday as number,
            startWeekOfMonthNumber = 5 as number, // 5 is the last week of the month
            startTime = #time ( 2, 0, 0 ) as time,
            DSTOffset = #duration ( 0, 13, 0, 0 ) as duration, // Offset in hours from UTC to Local during Daylight Saving Time

            // 2.2.3 Define Local Daylight Saving End Date and Time and Offset Paramaters
            endMonthNumber = 4 as number,
            endDayOfWeekNumber = Day.Sunday as number,
            endWeekOfMonthNumber = 1 as number, // 1 is the first week of the month
            endTime = #time ( 3, 0, 0 ) as time,
            StandardOffset = #duration ( 0, 12, 0, 0 ) as duration, // Offset in hours from UTC to Local during Standard Time
            // END Define Local Time Zone Specific Parameters

            // 2.2.4 Calculate Local DST Start Date relative to provided UTC Date/Time
            startMonthFirstDay = #date ( Date.Year ( UTCDateTime ), startMonthNumber , 1 ),
            startMonthLastDay = Date.EndOfMonth ( startMonthFirstDay ),
            startMonthFirstWeekDay = Date.DayOfWeek ( startMonthFirstDay, startDayOfWeekNumber ),
            startMonthLastWeekDay = Date.DayOfWeek ( startMonthLastDay, startDayOfWeekNumber ),

            // 2.2.5 Calculate Local DST End Date relative to UTC Date/Time
            endMonthFirstDay = #date ( Date.Year ( UTCDateTime ), endMonthNumber , 1 ),
            lastMonthFirstDay = Date.EndOfMonth ( endMonthFirstDay ),
            endMonthFirstWeekDay = Date.DayOfWeek ( endMonthFirstDay, endDayOfWeekNumber ),
            endMonthLastWeekDay = Date.DayOfWeek( lastMonthFirstDay, endDayOfWeekNumber ),

            // 2.2.6 Calculate DST Date/Time Start and Offset
            localDateTimeStartDST =
            (
                if startWeekOfMonthNumber = 5
                then ( startMonthLastDay - #duration ( Number.Mod ( 7 + startMonthLastWeekDay - startDayOfWeekNumber , 7 ), 0, 0, 0 ) ) & startTime // Last Week of Month
                else ( startMonthFirstDay + #duration ( 7 * ( startWeekOfMonthNumber - 1 ) + Number.Mod ( 7 + startDayOfWeekNumber - startMonthFirstWeekDay, 7 ), 0, 0, 0 ) ) & startTime // 1st, 2nd 3rd or 4th Week of Month
            ),

            UTCDateTimeStartDST = localDateTimeStartDST - StandardOffset,

            // 2.2.7 Calculate DST Dat/Time End and Offset
            localDateTimeEndDST =
            (
                if endWeekOfMonthNumber = 5
                then ( lastMonthFirstDay - #duration ( Number.Mod ( 7 + endMonthLastWeekDay - endDayOfWeekNumber, 7 ), 0, 0, 0 ) ) & endTime
                else ( endMonthFirstDay + #duration ( 7 * ( endWeekOfMonthNumber - 1 ) + Number.Mod ( 7 + endDayOfWeekNumber - endMonthFirstWeekDay, 7 ), 0, 0, 0 ) ) & endTime
            ),

            UTCDateTimeEndDST = localDateTimeEndDST - DSTOffset,

            // 2.2.8 Convert UTC Date/Time/Zone to Local Date/Time
            LocalDateTime =  UTCDateTime +
            (
                if UTCDateTimeStartDST < UTCDateTimeEndDST
                    // 2.2.8.1 Northern Hemisphere
                    then if UTCDateTime >= UTCDateTimeStartDST and UTCDateTime < UTCDateTimeEndDST
                        then DSTOffset
                        else StandardOffset

                    // 2.2.8.2 Southern Hemisphere
                    else if UTCDateTime >= UTCDateTimeEndDST and UTCDateTime < UTCDateTimeStartDST
                        then StandardOffset
                        else DSTOffset
            )
        in
            LocalDateTime,

    // 3.0 Custom UTC Date/Time to Local Date/Time Function
    UTCDateTimeZoneToLocalDateTime = ( UTCDateTimeZone as nullable datetimezone ) as nullable datetime =>
    let
        LocalDateTime =
            if ( UTCDateTimeZone = null)
            then null // 3.1 Return NULL
            else UTCDateTimeZoneToLocalDateTimeNotNull ( UTCDateTimeZone ) // 3.2 Convert UTC Date/Time to Local Date/Time
    in
        LocalDateTime,

    // 4.0 Invoke Custom UTC Date/Time to Local Date/Time Function
    #"UTC Date/Time To Local Date/Time" = Value.ReplaceType( UTCDateTimeZoneToLocalDateTime, FunctionType )
in
    #"UTC Date/Time To Local Date/Time"