Grouping with range

Hi NicoRey,

 

The solution assumes and requires your data to be sorted, like the sample dataset. When that is not the case, it has to be incorporated because it works through your table, comparing values row by row in the order they appear.

 

let
    Source = YourTable,
    Order =  Table.Buffer( Table.Sort(Source,{{"PN", Order.Ascending}})),
    Rows = Table.ToRows( Order),
    Result = Table.FromRows( List.Accumulate(
        List.Skip(Rows, 1),
        {Rows{0}},
        (state, current) => 
            let
                prevRow = List.Last(state),
                updateState =
                    if (prevRow{0} = current{0}) and (current{1} <= prevRow{2}) then
                        List.RemoveLastN(state, 1) & {{prevRow{0}, prevRow{1}, current{2}}}
                    else
                        state & {current}
            in
                updateState
    ), Table.ColumnNames(Source))
in
    Result

 

I hope this is helpful

Hi NicoRey, different approach:

 

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMjA0BNHGIDpWBypqbGgAETU2RxY1goqaQEWdQDxDqAmGID0YonBznSC2GYNFQcJKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PN = _t, FROM = _t, TO = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"FROM", Int64.Type}, {"TO", Int64.Type}}),

    fn_FromTo = 
        (myTable as table)=>
        let
            // _Detail = GroupedRows{[PN="A"]}[All],
            _Detail = myTable,
            SelectedColumnsBuffer = Table.Buffer(Table.SelectColumns(_Detail,{"FROM", "TO"})),
            Generate = List.Generate(
                ()=> [ x = 0, from = SelectedColumnsBuffer{x}[FROM], to = SelectedColumnsBuffer{x}[TO], y = from, z = to, w = 1 ],
                each [x] < Table.RowCount(SelectedColumnsBuffer),
                each [ x = [x]+1,
                    from = SelectedColumnsBuffer{x}[FROM], 
                    to = SelectedColumnsBuffer{x}[TO],
                    y = if from <= [to] then [from] else from,
                    z = to,
                    w = if from <= [to] then 1 else 0 ]
        ),
            ToTableInner = Table.FromRecords(Generate),
            FilteredRowsInner = Table.SelectRows(ToTableInner, each ([w] = 1)),
            RemovedOtherColumnsInner = Table.SelectColumns(FilteredRowsInner,{"y", "z"}),
            GroupedRowsInner = Table.Group(RemovedOtherColumnsInner, {"y"}, {{"All", each _, Int64.Type}, {"FROM", each List.First([y]), Int64.Type}, {"TO", each List.Last([z]), Int64.Type}}),
            RemovedOtherColumnsInner2 = [ a = Table.RemoveColumns(_Detail, {"FROM", "TO"}),
            b = Table.SelectColumns(GroupedRowsInner,{"FROM", "TO"}),
            c = Table.FromColumns(Table.ToColumns(a) & Table.ToColumns(b), Value.Type(a & b) )
        ][c],
            FilteredRowsInner2 = Table.SelectRows(RemovedOtherColumnsInner2, each ([FROM] <> null))
        in
            FilteredRowsInner2,

    GroupedRows = Table.Group(ChangedType, {"PN"}, {{"All", fn_FromTo, Int64.Type}}),
    CombinedAll = Table.Combine(GroupedRows[All])
in 
    CombinedAll