Table.Max

Table

Returns the row with the maximum value according to the given comparison criteria.

Examples on this page use shared sample tables. View them to understand the input data before reading the examples below.

Syntax

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

Parameters

NameTypeRequiredDescription
tabletableYesThe table to search.
comparisonCriteriaanyYesA column name (text), a list of column names, or a comparer function to determine ordering.
defaultanyNoOptional value to return if the table is empty. Defaults to null.

Return Value

any — The row with the highest value as a record, or the default if the table is empty.

Remarks

Table.Max returns the entire row (as a record) that contains the maximum value for the given comparison criteria. If multiple rows share the maximum value, the first one encountered is returned. Access individual fields from the result with record field syntax: Table.Max(t, "UnitPrice")[Product].

For the top N rows instead of just the top 1, use Table.MaxN. For just the maximum scalar value without needing the full row context, chain field access: Table.Max(t, "Quantity")[Quantity]. This is equivalent to List.Max(Table.Column(t, "Quantity")) but keeps the row context available.

If the table is empty, Table.Max returns null by default, or the specified default value. Always provide a default when the table may be empty to avoid null-propagation errors in downstream steps.

Examples

Example 1: Find the highest-priced order in the Sales table

let
    Sales = #table(
        {"OrderID", "CustomerName", "Product", "UnitPrice", "Quantity"},
        {{1,"Alice","Widget A",25.00,4},{2,"Bob","Gadget B",50.00,2},
         {6,"Bob","Thingamajig E",120.00,1},{4,"Alice","Gadget D",75.00,1}}
    )
in
    Table.Max(Sales, "UnitPrice")
Result
Result
1[OrderID = 6, CustomerName = "Bob", Product = "Thingamajig E", UnitPrice = 120, Quantity = 1]

Example 2: Extract just the product name with the highest unit price

let
    Sales = #table(
        {"OrderID", "CustomerName", "Product", "UnitPrice"},
        {{1,"Alice","Widget A",25.00},{2,"Bob","Gadget B",50.00},
         {6,"Bob","Thingamajig E",120.00},{4,"Alice","Gadget D",75.00}}
    )
in
    Table.Max(Sales, "UnitPrice")[Product]
Result
Result
1Thingamajig E

Example 3: Return a safe default when no East-region orders exist

let
    Sales = #table(
        {"OrderID", "CustomerName", "Region", "UnitPrice"},
        {{2,"Bob","West",50.00},{5,"Diana","West",25.00},{7,"Charlie","West",50.00}}
    ),
    EastSales = Table.SelectRows(Sales, each [Region] = "East")
in
    Table.Max(EastSales, "UnitPrice", [OrderID = 0, CustomerName = "N/A", Region = "East", UnitPrice = 0])
Result
Result
1[OrderID = 0, CustomerName = "N/A", Region = "East", UnitPrice = 0]

Related Functions

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks