Forum Discussion

Mann's avatar
Mann
Resolver III
6 years ago
Solved

Breaking a Date Range into 5 Min Interval Performance Issue

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 IdStart TimeEnd Time
A10/19/2019 8:02AM10/19/2019 8:18AM

 

Interval Table:

Individual IDStart TimeEnd TimeInterval
A10/19/2019 8:02AM10/19/2019 8:18AM10/19/2019 8:05AM
A10/19/2019 8:02AM10/19/2019 8:18AM10/19/2019 8:10AM
A10/19/2019 8:02AM10/19/2019 8:18AM10/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 

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago

    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's avatar
    ChrisMendoza
    6 years ago

    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.

  • Mann's avatar
    Mann
    6 years ago

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

10 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    6 years ago

    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's avatar
      Mann
      Resolver III
      6 years ago

      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's avatar
        ChrisMendoza
        Resident Rockstar
        6 years ago

        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's avatar
    ChrisMendoza
    Resident Rockstar
    6 years ago

    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.