Error in Power Query combining overlapping start end datetime fields

[Shadow61]

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 Token error when I paste it into Advanced editor.

This is sample data - rows 2,3,4 have overlapping periods so I want to combine them.

ID                Start                                 End
149551360 06-01-2025 09:59:46.484 06-01-2025 11:05:38.663
149551360 06-01-2025 13:22:59.137 06-01-2025 16:18:10.447
149551360 06-01-2025 16:01:57.474 06-01-2025 16:18:10.447
149551360 06-01-2025 16:01:57.474 06-01-2025 17:06:35.996
149551360 06-05-2024 09:18:14.330 06-05-2024 10:46:34.384
149551360 06-12-2024 12:27:32.169 06-12-2024 13:00:28.065

Combining Overlapping Start / End periods I would expect row 2,3 & 4 to be combined  to min(Start) & max(End) resulting in :

ID                Start                                 End
149551360 06-01-2025 09:59:46.484 06-01-2025 11:05:38.663
149551360 06-01-2025 13:22:59.137 06-01-2025 17:06:35.996
149551360 06-05-2024 09:18:14.330 06-05-2024 10:46:34.384
149551360 06-12-2024 12:27:32.169 06-12-2024 13:00:28.065

The issue arises when I paste in my code into Advanced Editor - I get a Token ',' expected, 4 lines from bottom.

I'm new to Power Query and not sure why it's occuring. I've moved the comma, removed it, no joy. Appreciate any input.

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 = Number.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

Thanks in Advance

[v-achippa]

Hi @Shadow61,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Mauro89, @ronrsnfld and @AlienSx for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Shadow61,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    types = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
    sort = Table.Sort(types, {"ID", "Start"}),
    rows = List.Buffer(Table.ToList(sort, each _)),
    gnr = List.Generate(
        () => [i = 0, r = rows{0}, min = r{1}, max = r{2}],
        (x) => x[i] < List.Count(rows),
        (x) => [
            i = x[i] + 1, 
            r = rows{i},
            next = r{0} <> x[r]{0} or r{1} > x[max],
            min = if next then r{1} else x[min],
            max = if next then r{2} else List.Max({r{2}, x[max]})
        ],
        (x) => {x[r]{0}, x[min], x[max]}
    ),
    result = Table.Group(
        Table.FromList(gnr, each _, {"ID", "Start", "End"}),
        {"ID", "Start"},
        {"End", (x) => Table.Last(x)[End]},
        GroupKind.Local
    )
in
    result

[Shadow61]

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,

[ronrsnfld · Accepted solution]

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]

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

[Mauro89]

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