Error in Power Query combining overlapping start end datetime fields

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

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

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,

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