cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mann Member
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Alternate to List.DateTimeZone function in M query

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

 

Capture.PNG

 

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"

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Alternate to List.DateTimeZone function in M query

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

 

Capture.PNG

 

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"

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Mann Member
Member

Re: Alternate to List.DateTimeZone function in M query

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

Re: Alternate to List.DateTimeZone function in M query

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.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)