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.

 

coathangers's avatar
coathangers
Frequent Visitor
3 years ago

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!)