The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
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 |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |