Values with the highest mark

[Hdema]

Hi

I have a table like this:

Ref. No.	Evaluation	Value
A	5	2
A	4	3
A	7	3
B	6	2
B	5	3
B	3	3
B	4	2

My goal is to group this data based on its maximum valuation through power query and get the following table:

Ref. No.	Max Evaluation	Value
A	7	3
B	6	2

I tried to group it by this:

= Table.Group(#"Previous step", {"Ref. No."}, {{"Max Evaluation", each List.Max([Evaluation]), type nullable number}})

But I get the Max Evaluation without its corresponding value

I would be gratefull for your help

Best regards

[Hdema]

Thank you very much Stéphane, it worked perfectly good!!!

[slorin]

Or with Table.Group

= Table.Group(
Source,
{"Ref. No."},
{{"Max", each List.Max([Evaluation]), type nullable number},
{"Value", each [Value]{List.PositionOf([Evaluation],List.Max([Evaluation]))}, type nullable number}}
)

Stéphane

[slorin · Accepted solution]

Hi

let
Source = YourSource,
Sort = Table.Sort(Source,{{"Evaluation", Order.Descending}}),
Distinct = Table.Distinct(Sort, {"Ref. No."})
in
Distinct

Stéphane