cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gvg Member
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: How 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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

7 REPLIES 7
MarcelBeug Super Contributor
Super Contributor

Re: How 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.

 

Specializing in Power Query Formula Language (M)

View solution in original post

gvg Member
Member

Re: How to filter by max value in power query

 

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 Super Contributor
Super Contributor

Re: How to filter by max value in power query

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

Specializing in Power Query Formula Language (M)
gvg Member
Member

Re: How to filter by max value in power query

Well, 0 starting index is a default value. You can actually choose whether you want to start from 0 or 1.

MarcelBeug Super Contributor
Super Contributor

Re: How to filter by max value in power query

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

Specializing in Power Query Formula Language (M)
gvg Member
Member

Re: How to filter by max value in power query

OK, understand. Thanks!

nirvana_moksh Established Member
Established Member

Re: How to filter by max value in power query

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 248 members 2,897 guests
Please welcome our newest community members: