Remove rows based on second key in another column

[Merleau]

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
35	XY-23	Prod2	C	1232
35	XY-23	Prod3	B	1233
36	XY-23	Prod1	B	1234
36	XY-23	Prod3	B	1235
36	XY-23	Prod4	Y	20
36	XY-23	Prod5	H	30
40	XY-100	Prod1	U	10
40	XY-100	Prod9		1
25	XY-100	Prod2	C	1236
25	XY-100	Prod9	B	1237

I want to group by Column2, then sort all records in Column1 and keep only the ones with the highest value in Column1.

Expected result should be:

Column1	Column2	Value1	Value2	Value3
36	XY-23	Prod1	B	1234
36	XY-23	Prod3	B	1235
36	XY-23	Prod4	Y	20
36	XY-23	Prod5	H	30
40	XY-100	Prod1	U	10
40	XY-100	Prod9		1

Can somebody pls help?

Thanks.

[Merleau]

Many thanks to @AlienSx  and @PhilipTreacy  for your quick responses. Both solutions work perfectly.

[AlienSx · Accepted solution]

    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])

[PhilipTreacy]

Hi @Merleau 

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