Forum Discussion
Solved
Alternate to List.DateTimeZone function in M query
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 colu...
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"
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"
- 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?- 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.
