Forum Discussion
Post ProdigyHow to filter by max value in power query
Example code:
let Source = #table(type table[Value = Int64.Type],List.Zip({{1..10}&{1..10}&{1..10}})), #"Filtered Rows" = Table.SelectRows(Source, each ([Value] = List.Max(Source[Value]))) in #"Filtered Rows"You can generate base code for the 2nd step by filtering on just some value and then adjust the generated code as in the example code.
Community ChampionExample code:
let
Source = #table(type table[Value = Int64.Type],List.Zip({{1..10}&{1..10}&{1..10}})),
#"Filtered Rows" = Table.SelectRows(Source, each ([Value] = List.Max(Source[Value])))
in
#"Filtered Rows"You can generate base code for the 2nd step by filtering on just some value and then adjust the generated code as in the example code.
Post ProdigyGreat. Thanks!
Found another way. First, sorted column Descending. Then added index column starting from 1. Then this formula
#"Added Index2" =Table.AddColumn(addindex, "xxx", each if addindex[HighestSatisfaction]{1}=
HighestSatisfaction] then [HighestSatisfaction] else null)
gave me the required result. But your way is shorter!
- MarcelBeug
Regarding your solution: you are comparing with the first runner up: {1} returns the second value.
Power Query is zero-based, so for the first value you should refer to {0}.
- gvg
Well, 0 starting index is a default value. You can actually choose whether you want to start from 0 or 1.
- MarcelBeug
That's correct, but you are still refering to the second row: {1} doesn't refer to a value, but to a row number.
Edit: so actually you don't need the Index column at all...