Forum Discussion

gvg's avatar
gvg
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

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 wi...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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.

     

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

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.

 

nirvana_moksh's avatar
nirvana_moksh
Icon for Impactful Individual rankImpactful Individual
8 years ago

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