Forum Discussion
Breaking a Date Range into 5 Min Interval Performance Issue
Mann -
Change to Duration.TotalMinutes:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Start Time],(Duration.TotalMinutes([End Time]-[Start Time])/5),#duration(0,0,5,0))),
I know you still need to work on the rounding to 5 minute increments; I just did what I readily knew how to do from other projects I've worked on.Mann -
Meh, there's an error at Midnight that needs to be addressed. It's closer though
Here you go, try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00De01DcyMLRUsLAyMFJw9EUXNLQACcbqRCs5QaUsIFKWVgaGGOotrQxNwepjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Individual Id" = _t, #"Start Time" = _t, #"End Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual Id", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Start Time],(Duration.TotalMinutes([End Time]-[Start Time])/5),#duration(0,0,5,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each Date.From([Custom])&Time.From(Number.RoundUp(288*Number.From(Time.From([Custom]))/1)/288)) in #"Added Custom1"Used the solution at https://community.powerbi.com/t5/Desktop/Round-Down-to-Nearest-30-Minute-Interval-Query-Editor/td-p/514248 for rounding and the video.
Let us know if it's faster and accomplishes your goal.
Hi ChrisMendoza
You are right there is no issue with the number of records this code is generating. If you see the highlighted part in the screenshot you provided:
Custom.1 should be: 10/20/2019 12:00 AM not 10/19/2019 since clock would move to next day at 12:00 AM.
I added this code for Custom.1 and it fixed the issue
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00De01DcyMLRUsLAyMHL0hYgZGULEDA0ggrE60UpOqFLYlAPFTKCqnVENNzQEKg+AKTeACBohlLtgKDe1QFcONMIAqtwVt9PRxECq3YhSbQZSHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Individual ID" = _t, #"Start Time" = _t, #"End Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual ID", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Start Time],(Duration.TotalMinutes([End Time]-[Start Time])/5),#duration(0,0,5,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each DateTime.From(Number.RoundUp(Number.From([Custom])*288,0)/288)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", type datetime}}) in #"Changed Type1"Thanks for your help on this. Appreciated!!
Mann -
Maybe this will start getting the ball rolling as a Power Query solution; see if the speed improves...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00De01DcyMLRUsLAyMFJw9EUXNLQACcbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Individual Id" = _t, #"Start Time" = _t, #"End Time" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual Id", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Start Time],(Duration.Minutes([End Time]-[Start Time])/5),#duration(0,0,5,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"gets you pretty close to your desired output:
Thanks for the suggestion.
In my dataset, I have Start Time and End Time going across days also. When I used your code it is not considering dividing the time range into 5 min group across days.
Do you know how that can be done?
Thanks.
Mann -
Change to Duration.TotalMinutes:
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Start Time],(Duration.TotalMinutes([End Time]-[Start Time])/5),#duration(0,0,5,0))),
I know you still need to work on the rounding to 5 minute increments; I just did what I readily knew how to do from other projects I've worked on.Thanks ChrisMendoza
It worked perfectly.
I will check the solution for rounding and will let you know if it has improved the performance. Seems like it will improve.
Thanks
Mann -
Alright, I addressed the Midnight bit using TRY ... OTHERWISE
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00De01DcyMLRUsLAyMFJw9EUXNLQACcbqRCs5QaUsIFKWVgaGGOotrQxNwepjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Individual Id" = _t, #"Start Time" = _t, #"End Time" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Individual Id", type text}, {"Start Time", type datetime}, {"End Time", type datetime}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([Start Time],(Duration.TotalMinutes([End Time]-[Start Time])/5),#duration(0,0,5,0))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each try Date.From([Custom])&Time.From(Number.RoundUp(288*Number.From(Time.From([Custom]))/1)/288) otherwise Date.From([Custom])&Time.FromText("12:00:00 AM")) in #"Added Custom1"Should be good.