Grouping with range

[NicoRey]

Hello,

I have a table with validaty range (FROM / TO) for the first column PN.

Below is an example.

I want to group in PowerQuery based on Column PN showing continuous validity ranges.

PN FROM    TO
A   22011    23011
A   23101    23371

A   23201    23471
B   21011    21101
B   21011    23011
B   22031    22301

Wanted result:

PN FROM   TO
A    22011   23011
A    23101   23471
B    21011   22301

I do not know if this possible to achieve.

Br

Nico

[m_dekorte · Accepted solution]

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

[dufoq3 · Accepted solution]

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

[m_dekorte]

Hi @NicoRey 

Give this a go:

let
    Source = YourTable,
    Rows = Table.ToRows(Source),
    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

produces this result

I hope this is helpful

[NicoRey]

Hi,

Thx for the reply.

I cannot say I understand the coding but copied it to try out.

Added an additional row for PN "A" with range from 23111 to 23461 with following result.

I my wanted solution this row doesn't show in the result as it "fits"  in the range of row 2 from 23101 to 23471.

Any ideas?

[m_dekorte · Accepted solution]

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