Table.Max - Table Function

Table.Max is a Power Query M function that finds the largest row in a table based on comparison criteria. The function returns the largest row, or an optional default value if the table is empty.

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

Syntax

Table.Max(
   table as table,
   comparisonCriteria as any,
   optional default as any,
) as any

Description

The Table.Max function returns the row in a table with the largest value based on a specified column or custom condition. It offers flexibility in how you find the maximum:

Column-based: You can specify a column, and the function will return the row with the highest value in that column.
Custom condition: Instead of choosing a column, you can define your own rule for what counts as the “maximum,” such as using calculations or using custom logic.

If the table is empty, you can also provide an optional fallback value to ensure the function returns a result rather than an error.

Examples

In this example, we have a table that tracks different books and how many times each has been borrowed. Let’s say you want to find the book that has been borrowed the most.

Here’s the table of books:

Table.First dataset as start in Power Query M

To find the book that has been borrowed the most, we use Table.Max and specify the TimesBorrowed column:

Table.Max( Source, "TimesBorrowed" )

This function tells Power Query to look at the TimesBorrowed column and return the row with the highest value, which is 60. The result will be:

Table.Max returns the maximum value from a column in Power Query M

If multiple rows have identical maximum values, only one of the rows will be returned as a record.

Finding the Maximum Using a Condition

You can also define your own logic to determine the maximum. In the next example, instead of simply finding the largest number in a specific column, we create a custom rule using the Index column.

Here’s the function:

Table.Max( Source, each - [Index] )

In this case, we ask Power Query to find the row with the smallest index by treating the Index column as negative (with each - [Index] ). This trick reverses the comparison, and the function returns the row with the smallest Index, which is 1. The result will be:

Table.Max returns the maximum value using a condition in Power Query M

Handling Empty Tables: Using a Fallback Value

What if the table is empty? By default, Table.Max wouldn’t return any result when there’s no data, but you can specify a fallback value to be used in such cases.

In this example, we create an empty table and ask for the maximum value in a column:

let
  EmptyTable = #table( type table[ myColumn = text ], {} ),
  TableMaxFallbackValue = Table.Max( EmptyTable, "myColumn", "FallbackValue" )
in
  TableMaxFallbackValue

Since the table is empty, Power Query returns the fallback value, FallbackValue, because it can’t find any data in the "myColumn" column. This makes sure the function returns something useful, even if the table has no rows.

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 }
      }
    ),
    TableMaxColumn = Table.Max( Source, "TimesBorrowed" ),
    TableMaxCondition = Table.Max( Source, each -[Index] ),
    EmptyTable = #table( type table[ myColumn = text], {} ),
    TableMaxFallbackValue = Table.Max( EmptyTable, "myColumn", "FallbackValue" )
in
    TableMaxFallbackValue

Learn more about Table.Max in the following articles:

How to Group By Maximum Value using Table.Max – Power Query
Working with table objects is a key skill to learn. This article explains how you can reached into grouped data using Table.Max function in Power Query. » Read more

Other functions related to Table.Max are:

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