Forum Discussion

Helper II

2 years ago

Solved

Remove rows based on second key in another column

Hello, I need help filtering out data in a large dataset. I have a table that looks like this: Column1 Column2 Value1 Value2 Value3 35 XY-23 Prod1 A 1231 ...

2 years ago

    gr = Table.Group(
        your_tbl, 
        "Column2", 
        {"x", (x) => 
            [a = List.Max(x[Column1]), 
            b = Table.SelectRows(x, (w) => w[Column1] = a)][b]}),
    z = Table.Combine(gr[x])

Super User

2 years ago

Download example PBIX file

Duplicate your starting table.

With this table group by Column2 and choose Max of Column1 as the aggregation. name the new column Column1

Which gives

Select this new table and then Merge it with the original table, using Column1 as the matching column

Giving this

Click the double headed arrow in the column header to expand the tables, only choosing the value columns

Rearrange columns as needed

Please check my PBIX file (linked above) for this example data and queries.

Regards

Phil

Related content