Forum Discussion

Shadow61's avatar
Shadow61
New Member
7 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
    7 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.

Mauro89's avatar
Mauro89
Icon for Super User rankSuper User
7 months ago

Hi Shadow61,

 

The issue could be in the Number.Max function - try List.Max. Also, there's a syntax issue with the record field definition. Here's another code version you can try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed = Table.TransformColumnTypes(Source,
        {{"Id", type text}, {"Start", type datetime}, {"End", type datetime}}),
    
    Sorted = Table.Sort(Typed, {{"Id", Order.Ascending}, {"Start", Order.Ascending}}),
    
    Grouped = Table.Group(Sorted, {"Id"}, {
        {"Merged", (t) =>
            let
                Rows = Table.ToRecords(t),
                Merged = List.Accumulate(
                    Rows,
                    {},
                    (state, current) =>
                        if List.IsEmpty(state) then
                            { [Start=current[Start], End=current[End]] }
                        else
                            let
                                last = List.Last(state),
                                overlaps = current[Start] <= last[End]
                            in
                                if overlaps then
                                    List.ReplaceRange(
                                        state,
                                        List.Count(state)-1, 
                                        1,
                                        { [Start = last[Start], End = List.Max({last[End], current[End]})] }
                                    )
                                else
                                    state & { [Start=current[Start], End=current[End]] }
                ),
                Output = Table.FromList(Merged, Splitter.SplitByNothing(), {"Range"}),
                Expand = Table.ExpandRecordColumn(Output, "Range", {"Start", "End"})
            in
                Expand, 
            type table 
        }
    }),
    Final = Table.ExpandTableColumn(Grouped, "Merged", {"Start", "End"})
in
    Final

 

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution