Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.