Power Query keep only max records

[cr731]

I have a table with one column containing many duplicate values, and a second column with values. I want to filter my table so column 1 is all of the unique values corresponding with the max of column 2.

So I might have,

Col1, Col2
Yellow, 4
Yellow, 6
Red, 1
Red, 4
Blue, 2

I want my resulting table to be

Yellow, 6
Red, 4
Blue, 2

I've tried using a self-join, aggregating by max on Col2 and then keeping only the max values, but it's really slow. Are there any better approaches?

 

[ImkeF]

When removing duplicates PQ will keep the first lines. So ordering by Col2 (1st) and Col1 (2nd) before removing duplicates should do the job.
But you have to wrap your sorting result into a Table.Buffer(...), as often the sorting will not be kept (especially if you load to the Data model).

 

[anvg]

Hi
Let your table with columns (Col1, Col2) has a name - table1

maxOnly = Table.Group(table1, {"Col1"}, { {"Col2", each List.Max(_[Col2]), type number} }, GroupKind.Global)

Regards,