Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.