Forum Discussion
Post ProdigyHow to filter by max value in power query
Hi experts,
Could you help me to figure out how to filter a column by max value in that column in Power Query or Power BI query editor? The problem is that I do not know beforehand what max value will arrive in the table's column. I've been trying out to make a new column with the maximum value with the help of List.Max and Table.Max but they do not seem to accept a column as an argument.
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.
11 Replies
- MarcelBeug
Community Champion
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.
gvgGreat. 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}.
nirvana_mokshImpactful Individual
Is there a way in M that we can make it evaluate based on a ID and Insert Date, meaning if ID 123 is repeated 5 times with following insert dates:
6/10/18 11:10 PM
6/12/18 10:10 PM
6/13/18 9:10 AM6/14/18 10:10 AM
6/14/18 12:10 PMIt returning the 5 th Row Only, basically Group By in a way
Thanks a lot that helped me too!!
