Downloads
Power Query MTable: rowsIntermediate

Table.Max

Returns the row that has the highest value in a column.

Syntax
Table.Max(table as table, column as text, optional default as any) as any

Returns: The row as a record, or the default value if the table is empty.

What it does

Table.Max finds the single row with the highest value in one column and returns the whole row. The row comes back as a record, so you can read any field of it with square brackets. Use it to find the latest date, the largest payment or the highest balance, without sorting the whole table.

Syntax and arguments

Example

A cash manager needs the date of the most recent customer payment. Table.Max returns the latest payment row, and its payment date is read from it.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Payments = Source{[Schema = "classicmodels", Item = "payments"]}[Data],
    Latest = Table.Max(Payments, "paymentDate", null),
    LatestDate = Latest[paymentDate]
in
    LatestDate

The first step finds the row with the latest payment date. The second step reads the date from that row. The date column must be a true date for the comparison to be right.

Example: a second, different use

The largest single payment in the sample data is found, so it can be checked against an approval limit.

let
    Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
    Payments = Source{[Schema = "classicmodels", Item = "payments"]}[Data],
    Largest = Table.Max(Payments, "amount", null)
in
    Largest

Things to watch

In business terms

The latest posting date, the largest payment and the highest balance are common checks in cut-off and approval testing. Finding them with Table.Max makes the test repeatable.

Related functions

All Power Query M functions · View as Markdown · M basics