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!!
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.
Hi ChrisMendoza
Thanks for helping me out here. Everything works fine so far except one thing.
I used your Try and Otherwise logic. Issue with this is it also inserting 12:00AM record for start date if start time of that date is more than 12 AM. As below:
Any idea how this will be sorted out.
Mann.
Mann -
I don't understand what you mean. [Individual Id] = "C" returns 37 rows.
[Custom] is used to determine the dynamic count of rows to create/insert between [Start Time] and [End Time] in 5 minute intervals. [Custom.1] then rounds up [Custom]. The Try Otherwise is meant to handle the occurrences where 'Date1 11:57:00 PM' is rounded to 'Date2 12:00:00 AM'. [Custom.1] sorted looks correct.If you are loading to Power BI, sort order is not guaranteed as described @ https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390 so possibly that is the confusion?
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!!