Breaking a Date Range into 5 Min Interval Performance Issue

[Mann]

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 

[ChrisMendoza · Accepted 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.

[ChrisMendoza · Accepted solution]

@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.

[ChrisMendoza · Accepted solution]

@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.

[ChrisMendoza]

@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:

[Mann]

@ChrisMendoza 

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.

[ChrisMendoza · Accepted 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.

[Mann]

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

[ChrisMendoza]

@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.

[Mann]

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.

[ChrisMendoza]

@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?

[Mann · Accepted solution]

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!!

[ChrisMendoza]

@Mann - Ha, I missed that completely.