Forum Discussion

Ms_2's avatar
Ms_2
Frequent Visitor
8 years ago
Solved

Query Editor Filter column based on max value (list.max)

Hello and thank you for reading.

I'm trying to filter a table in the advanced query editor based on the max value in a specific column.

 Here's the M:
~~~~

= Table.SelectRows(#"Changed Type1", each [Week by Year] = List.Max(#"Changed Type1", [Week by Year]))

~~~~

#"Changed Type1" is the previous line of code

[Week by Year] is the column i'm trying to filter.

The error looks like this:
Expression.Error: We cannot convert a value of type Table to type list.
Details:
    Value=Table

    Type=Type

This was my first attempt:

~~~

= Table.SelectRows(#"Changed Type1", each [Week by Year] = Max([Week by Year]))

~~~

Thanks for your help!

  • Hi,

     

     

    Just a slight change:

     

     

    = Table.SelectRows(#"Changed Type1", each [Week by Year] = List.Max(#"Changed Type1"[Week by Year]))

    List.Max requires a list (such as a Table[Column]) as its first argument.

     

    Owen

     

3 Replies

  • Hi,

     

     

    Just a slight change:

     

     

    = Table.SelectRows(#"Changed Type1", each [Week by Year] = List.Max(#"Changed Type1"[Week by Year]))

    List.Max requires a list (such as a Table[Column]) as its first argument.

     

    Owen

     

    • Ms_2's avatar
      Ms_2
      Frequent Visitor

      Like this?:

       

      = Table.SelectRows(#"Changed Type1", each [Week by Year] = Table[Week by Year].List.Max(#"Changed Type1", [Week by Year]))

      • OwenAuger's avatar
        OwenAuger
        Super User

        No - just use the exact code from my post. It's the same as the code at the top of your first post with a comma removed.