Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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!)
Thanks mate! Helped me out too! Nice one!
Thanks a lot that helped me too!!
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
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!
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}.
Well, 0 starting index is a default value. You can actually choose whether you want to start from 0 or 1.
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...
OK, understand. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |