cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
colinmaitland
New Member

Power Query M Function - Nested - UTCDateTime to LocalDateTime

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.

Issue Image 1.jpg

 

 

 

 

 

 

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
colinmaitland
New Member

Re: Power Query M Function - Nested - UTCDateTime to LocalDateTime

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"

 

View solution in original post

1 REPLY 1
Highlighted
colinmaitland
New Member

Re: Power Query M Function - Nested - UTCDateTime to LocalDateTime

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"

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors