Forum Discussion

Shadow61's avatar
Shadow61
New Member
6 months ago
Solved

Error in Power Query combining overlapping start end datetime fields

Sorry , new to Power Query. I have a table of records where for a given ID, I want to merge then if the Startdatetime and Enddatetime overlap. Copilot gave me some sample code, but it gives me a To...
General Comment
  • ronrsnfld's avatar
    ronrsnfld
    6 months ago

    You can try this Power Query code, which seems to be a bit faster:

    let

//Change next line to reflect actual data source
    Source = Sheet1,
    // Sort by StartDate, then EndDate
    Sorted = Table.Sort(Source,{{"Start", Order.Ascending}, {"End", Order.Ascending}}),
    
    // Add index
    Indexed = Table.AddIndexColumn(Sorted, "Index", 0, 1),
    
    // Group and merge overlapping ranges
    Grouped = Table.Group(Indexed, {"ID"}, 
        {{"Merged", (t) =>
            List.Accumulate(
                Table.ToRecords(t),
                {},
                (state, current) =>
                    if List.IsEmpty(state) then
                        {[Start = current[Start], End = current[End]]}
                    else
                        let
                            last = List.Last(state),
                            remaining = List.RemoveLastN(state, 1)
                        in
                            if current[Start] <= last[End] then
                                remaining & {[Start = last[Start], 
                                             End = List.Max({last[End], current[End]})]}
                            else
                                state & {[Start = current[Start], End = current[End]]}
            )
        , type table[Start=datetime, End=datetime]}}
    ),
    
    Expanded = Table.ExpandTableColumn(Grouped, "Merged", {"Start", "End"})
in
    Expanded

     

    In VBA, Application.ScreenUpdating = False will disable writing while the macro is being executed. But if you do the entire logic within VBA, that wouldn't be necessary. But for large data sets, I would think Power Query would be faster for a number of reasons.

Shadow61's avatar
Shadow61
New Member
6 months ago

Hi Mauro99,

Your code did work on my sample data set. 

Now I have full dataset of 300k records which seemed to run in the Power Query.

Saving back to a worksheet however did about 23K records in 1 hour.!  Maybe Power Query isn't the answer to my problem.. OR is there some command like in VBA which does the calculations but not refresh the screen until it is finished.

 

thanks 

 

regards,

  • Mauro89's avatar
    Mauro89
    Super User
    6 months ago

    Shadow61, ok loading this into a sheet indeed can cause trouble 😅

    And Iam not aware of a similar Power Query capability for not refreshing. 

    If possible you can try to move the logic more upstream if it's coming from a SQL DB to do it there and just consume the data in Power query. 

    Hope this helps!

     

    Best regards!

     

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    6 months ago

    You can try this Power Query code, which seems to be a bit faster:

    let

//Change next line to reflect actual data source
    Source = Sheet1,
    // Sort by StartDate, then EndDate
    Sorted = Table.Sort(Source,{{"Start", Order.Ascending}, {"End", Order.Ascending}}),
    
    // Add index
    Indexed = Table.AddIndexColumn(Sorted, "Index", 0, 1),
    
    // Group and merge overlapping ranges
    Grouped = Table.Group(Indexed, {"ID"}, 
        {{"Merged", (t) =>
            List.Accumulate(
                Table.ToRecords(t),
                {},
                (state, current) =>
                    if List.IsEmpty(state) then
                        {[Start = current[Start], End = current[End]]}
                    else
                        let
                            last = List.Last(state),
                            remaining = List.RemoveLastN(state, 1)
                        in
                            if current[Start] <= last[End] then
                                remaining & {[Start = last[Start], 
                                             End = List.Max({last[End], current[End]})]}
                            else
                                state & {[Start = current[Start], End = current[End]]}
            )
        , type table[Start=datetime, End=datetime]}}
    ),
    
    Expanded = Table.ExpandTableColumn(Grouped, "Merged", {"Start", "End"})
in
    Expanded

     

    In VBA, Application.ScreenUpdating = False will disable writing while the macro is being executed. But if you do the entire logic within VBA, that wouldn't be necessary. But for large data sets, I would think Power Query would be faster for a number of reasons.