Table.Max Function in Power Query M (4 Easy Examples)

If you want to find the row with the highest value in a Power Query table, like the region with the most units sold, the Table.Max function is what you reach for. You give it a table and a way to compare rows, and it hands back the single top row. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Table.Max Function

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

where

table (required, table). The table to scan for the largest row.
comparisonCriteria (required, any). How rows are ranked. Pass a column name as text ("Units"), a list of column names to sort by several keys, or an each expression that computes the value to rank by (each [Units]*[Price]).
default (optional, any). The value returned when the table is empty. Without it, an empty table throws an error.

Returns: the largest row as a record (the whole winning row, not a single number). To get one value out of it, access a field like [Region]. If the table is empty, it returns default.

In plain terms, you give it a table and tell it how to rank the rows, and it returns the top row.

Example 1: Find the top row by a column

Say you have a RegionSales table of coffee-shop sales by region:

Region	Units	Price
North	120	4.50
South	80	9.00
East	150	4.00
West	95	5.50

You want the region that sold the most units. Type the number columns first, rank by "Units", then pull the Region field off the winning row:

let
    Source = Excel.CurrentWorkbook(){[Name="RegionSales"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units", Int64.Type},{"Price", type number}}),
    TopRow = Table.Max(Typed,"Units"),
    Result = TopRow[Region]
in
    Result

Result: East

East has the most units (150), so Table.Max returns its row, and [Region] reads the name off it.

Example 2: See the whole winning row

Table.Max returns a record, the entire top row, not just one value. To see all of it, wrap the result in Record.ToTable, which lays the record out as a Name/Value table.

Using the same RegionSales shape:

Region	Units	Price
North	120	4.50
South	80	9.00
East	150	4.00
West	95	5.50

let
    Source = Excel.CurrentWorkbook(){[Name="RegionSales2"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units", Int64.Type},{"Price", type number}}),
    TopRow = Table.Max(Typed,"Units"),
    Result = Record.ToTable(TopRow)
in
    Result

The result shows every field of the East row:

Name	Value
Region	East
Units	150
Price	4

Because Table.Max gives you the row, you can read any column off it, not just the one you ranked by.

Example 3: Rank by a computed value

comparisonCriteria does not have to be a plain column. Pass an each expression to rank by something you calculate per row, like revenue ([Units]*[Price]).

Using the same RegionSales data, rank by revenue and read the Region:

let
    Source = Excel.CurrentWorkbook(){[Name="RegionSales3"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Units", Int64.Type},{"Price", type number}}),
    TopRow = Table.Max(Typed,each [Units]*[Price]),
    Result = TopRow[Region]
in
    Result

Result: South

South wins on revenue (809.00=720) even though East moved the most units (1504.00=600). Ranking by a computed value can pick a different winner than ranking by a single column. If you need the full ranked order rather than just the top row, sort the table instead.

Example 4: Handle an empty table with a default

On an empty table, Table.Max throws an error unless you pass the third argument. The default value is what comes back when there are no rows to rank.

let
    Empty = #table(type table [Region=text,Units=number],{}),
    Result = Table.Max(Empty,"Units",-1)
in
    Result

Result: -1

There are no rows, so Table.Max returns the default of -1 instead of failing. Use a fallback that makes sense downstream, like -1, null, or a placeholder record.

Things to keep in mind with Table.Max

Untyped number columns compare as text. Columns from Excel.CurrentWorkbook arrive as type any, and Table.Max then ranks them lexically, so "80" can beat "150". Set the column to a number type first with Table.TransformColumnTypes (as every example above does).
It returns the row, not the value. You get a record back, so reading the result directly gives you the whole row. Field-access it ([Region]) or convert it with Record.ToTable to get something usable (Examples 1 and 2).
An empty table errors without a default. Calling Table.Max(EmptyTable,"Units") on a table with no rows throws an Expression.Error. Pass the third argument to get a safe fallback instead (Example 4).
Ties return one arbitrary row. If two rows share the maximum, only one record comes back, and which one is not guaranteed. Add a tie-breaker column if you need a deterministic result.

Common questions about Table.Max

What is the difference between Table.Max and List.Max?

List.Max takes a list of values and returns the single largest value, like List.Max({3,9,5}) giving 9. That is the approach to use when you just want to find the maximum value in a column.

Table.Max takes a table plus a ranking criterion and returns the whole winning row as a record. Reach for List.Max when you already have one column of values, and Table.Max when you need the rest of the row that owns the maximum.

List of All Power Query Functions

Related Power Query Functions / Articles: