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 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.

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"

     

     

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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.