The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Guys,
I worked out a solution for the below requirement:
There is source table which has date ranges in it like Start Time and End Time for different Individuals. Requirement is to break these date ranges into groups of 5 min. Breaking of record should start from first available standard 5 min interval as shown:
Source Table:
Individual Id | Start Time | End Time |
A | 10/19/2019 8:02AM | 10/19/2019 8:18AM |
Interval Table:
Individual ID | Start Time | End Time | Interval |
A | 10/19/2019 8:02AM | 10/19/2019 8:18AM | 10/19/2019 8:05AM |
A | 10/19/2019 8:02AM | 10/19/2019 8:18AM | 10/19/2019 8:10AM |
A | 10/19/2019 8:02AM | 10/19/2019 8:18AM | 10/19/2019 8:15AM |
I created below code for this:
EVALUATE
VAR _SourceNeeded =
SELECTCOLUMNS (
Source,
"Individual ID", Source[Individual ID],
"Start Time", [Start Time],
"End Time", [End Time]
)
VAR _Interval =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATESERIES ( 0, 1440, 5 ),
"TimeValue", TIME ( 0, [Value], 0 )
),
"IntervalBreaks", [TimeValue]
)
RETURN
FILTER (
ADDCOLUMNS (
GENERATE ( _SourceNeeded, _Interval ),
"DateTime", DATE ( YEAR ( [Start Time] ), MONTH ( [Start Time] ), DAY ( [Start Time] ) ) + [IntervalBreaks]
),
IF (
[DateTime] >= [Start Time]
&& [DateTime] <= [End Time],
FORMAT ( [DateTime], "mm/dd/yyyy" )
= FORMAT ( [Start Time], "mm/dd/yyyy" )
&& [Start Time] <= [DateTime]
&& [End Time] > [DateTime],
FORMAT ( [DateTime], "mm/dd/yyyy" )
= FORMAT ( [Start Time], "mm/dd/yyyy" )
&& [Start Time] >= [DateTime]
&& [End Time] > [DateTime]
&& [End Time]
< [DateTime] + TIME ( 0, 5, 0 )
)
)
This code is giving the expected output but it blows the memory because of CROSSJOIN I used. For roughly 100K records in Source it takes around 5 min.
How can we improvise this logic? Can we re-write this logic without using CROSSJOIN?
@Zubair_Muhammad , @MattAllington
Thanks
Solved! Go to Solution.
@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.
Proud to be a Super User!
@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/... for rounding and the video.
Let us know if it's faster and accomplishes your goal.
Proud to be a Super User!
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 -
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/... for rounding and the video.
Let us know if it's faster and accomplishes your goal.
Proud to be a Super User!
@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:
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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-i... so possibly that is the confusion?
Proud to be a Super User!
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 - Ha, I missed that completely.
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
138 | |
106 | |
103 | |
74 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
86 |