How to filter by max value in power query

[gvg]

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.

[MarcelBeug · Accepted solution]

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.

[coathangers]

I must be missing something as I can't translate that code to my use case.

If there are other users like me in the same position, then hopefully this will work on any table:

let
    Source = TableName,
    MaxValue = List.Max(Source[TableColumn]),
    Source1 = Source,
    IsMaxLogical = Table.AddColumn(Source1, "IsMax", each if [TableColumn] = MaxValue then true else false, type logical),
    #"Filtered Rows" = Table.SelectRows(IsMaxLogical, each [IsMax] = true)
in
    #"Filtered Rows"

Kind of like what the OP would acheive but with extra steps (for the uninformed!)

[arifulice09]

Thanks for nice solution.@MarcelBeug

[Anonymous]

Thanks mate!  Helped me out too!  Nice one!  

[isamchakur]

Thanks a lot that helped me too!!

[nirvana_moksh]

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 AM

6/14/18 10:10 AM
6/14/18 12:10 PM

It returning the 5 th Row Only, basically Group By in a way

[gvg]

Great. 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...

[gvg]

OK, understand. Thanks!