Forum Discussion

Mann's avatar
Mann
Resolver III
6 years ago
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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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's avatar
Mann
Resolver III
6 years ago
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's avatar
Mann
Resolver III
6 years ago
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.