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.
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}.
- gvg
Well, 0 starting index is a default value. You can actually choose whether you want to start from 0 or 1.
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!!
Thanks mate! Helped me out too! Nice one!
- arifulice09
Helper I
Thanks for nice solution.MarcelBeug
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!)
