Alternate to List.DateTimeZone function in M query

[Mann]

Hi Guys,

I am finding 5 min interval records for date range records (in datetime format) for which I am using List.DateTime function so far in Power query. This function works well in Custom column as shown below:

=List.DateTimes([StartTime],(Duration.TotalMinutes([EndTime]-StartTime])/5+1),#duration(0,0,5,0))

Now, I am getting date time fields in my model as datetimezone format like:

For UTC: e.g. 2019-02-17T20:00:17.001+0000

For Local (EST): e.g. 2019-02-17T15:00:17.001000-05:00

How can I run the same for datetimezone fields (StartTime and EndTime)

I checked we can use List.DateTimeZones function for this but somehow this function doesn't work for me. Is there any other way to divide datetimezone range into intervals of 5min in M Query.

Please advice.

[v-frfei-msft · Accepted solution]

Hi @Mann ,

Please add a custom column as below.

=List.DateTimeZones([start date], Duration.Minutes(Duration.From([end date]-[start date]))/5+1, #duration(0, 0, 5, 0))

M code for your reference as well.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ3MAQiBUMjKwMDIFLSQRM1BkkoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"start date" = _t, #"end date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"start date", type datetime}, {"end date", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"start date", type datetimezone}, {"end date", type datetimezone}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.DateTimeZones([start date], Duration.Minutes(Duration.From([end date]-[start date]))/5+1, #duration(0, 0, 5, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

[Mann]

Hi @Frank

Thanks for the reply on this.

Actually my model exist on Azure analysis services and some how when I use List.DateTimeZones function there it doesnt recoganise it. I am planning to use List.DateTime only after finding the DateTime Value from timezone fields.

Can we do like this?

[Mann]

As Azure analysis services was not recoganizing List.DateTimeZones function so I calculated Datetime value from datetimezone field and then used List.DateTime function.
It worked! For Power BI List.DateTimeZones function is working fine.