Table.MaxN - Table Function

Table.MaxN is a Power Query M function that retrieves the largest row(s) in a table based on comparison criteria and a count or condition. The function returns a list of rows that meet the specified criteria.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.MaxN(
   table as table,
   comparisonCriteria as any,
   countOrCondition as any,
) as table

Description

The Table.MaxN function returns the larges rows from a table based on a specified column or condition. It sorts the rows by the given criteria, then filters the result based on the third argument. Note the sorting algorithm cannot guarantee a fixed sorted result.

You can use one of the following approaches for the countOrCondition parameter:

If a number is provided, the function returns up to that many items in descending order.
If a condition is provided, the function returns items that meet the condition, stopping as soon as one does not match.

Examples

Let’s explore a couple of examples to see how the Table.MaxN function works.

Finding the Four Largest Values in a Column

In this example, we have a table of books along with details about how many times each book has been borrowed. Our goal is to find the four books that have been borrowed the most.

Here’s the table of books:

Table.First dataset as start in Power Query M

To find the four books with the highest TimesBorrowed values, we use the following function:

Table.MaxN( Source, "TimesBorrowed", 4 )

This tells Power Query to look at the TimesBorrowed column and return the four rows with the largest values. The result will be:

Table.MaxN returns maximum rows based on count in Power Query M

These are the four books that have been borrowed the most times. The operation does both filtering and sorting at the same time.

It’s important to remember that if 5 rows would represent the lowest four values, the function still only returns four rows and does not guarantee a fixed sorted result.

Using a Custom Condition to Find Rows

In the next example, we use a custom condition to find all books that have been borrowed more than 40 times. Rather than specifying a fixed number of results, we define a rule for the function to follow.

Here’s the function:

Table.MaxN( Source, "TimesBorrowed", each [TimesBorrowed] > 40 )

This tells Power Query to return all rows where the TimesBorrowed value is greater than 40. The function will return:

Table.MaxN returns maximum rows based on condition in Power Query M

These three books meet the condition of being borrowed more than 40 times.

Full M Code

To try this yourself, please paste the following code into the advanced editor:

let
  Source = 
    #table(
      type table [ Index = Int64.Type, Title = text, Genre = text, TimesBorrowed = Int64.Type ],
      {
          { 1, "1984",                                  "Dystopian", 30 },
          { 2, "To Kill a Mockingbird",                 "Fiction",   50 },
          { 3, "The Great Gatsby",                      "Fiction",   45 },
          { 4, "Harry Potter and the Sorcerer's Stone", "Fantasy",   60 },
          { 5, "The Hobbit",                            "Fantasy",   35 },
          { 6, "Pride and Prejudice",                   "Romance",   40 },
          { 7, "The Catcher in the Rye",                "Fiction",   20 },
          { 8, "The Shining",                           "Horror",    25 }
      }
    ),
    TableMaxN_WithCount = Table.MaxN( Source, "TimesBorrowed", 4 ),
    TableMaxN_WithCondition = Table.MaxN( Source, "TimesBorrowed", each [TimesBorrowed] > 40 )
in
    TableMaxN_WithCondition

Other functions related to Table.MaxN are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-maxn