Forum Discussion
Solved
Values with the highest mark
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
Hi
let
Source = YourSource,
Sort = Table.Sort(Source,{{"Evaluation", Order.Descending}}),
Distinct = Table.Distinct(Sort, {"Ref. No."})
in
DistinctStéphane
3 Replies
Hi
let
Source = YourSource,
Sort = Table.Sort(Source,{{"Evaluation", Order.Descending}}),
Distinct = Table.Distinct(Sort, {"Ref. No."})
in
DistinctStéphane
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
Thank you very much Stéphane, it worked perfectly good!!!
