Table.Max

Updated on

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

Table.Max returns the largest row or default value using the given criteria. It selects the largest row in table based on comparisonCriteria or returns default if empty.

Examples

Find the row with the largest value in column [a] in the table ( {[a = 2, b = 4], [a = 6, b = 8]} ).

// Output: [a = 6, b = 8]
Table.Max( 
    Table.FromRecords( {
        [a = 2, b = 4],
        [a = 6, b = 8]
    } ),
    "a"
 )

Find the row with the largest value in column [a] in the table ( {} ). Return -1 if empty.

// Output: -1
Table.Max( #table( {"a"}, {} ), "a", -1 )

Learn more about Table.Max in the following articles:

Other functions related to Table.Max are:

BI Gorilla Blog

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