Table.Max
Returns the row that has the highest value in a column.
Table.Max(table as table, column as text, optional default as any) as anyReturns: 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
- table: the table to search. Required.
- column: the name of the column to compare, in quotes. Required.
- default: the value to return if the table is empty, such as
null. Optional, but advisable when the table might be empty.
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
- The result is a record, not a single value. Read a field from it, such as
[amount]. - If the table can be empty, give a default record of the same shape. Otherwise the field read in the next step fails.
- When two rows tie, the one returned depends on the order of the table. Sort and check if the tie matters.
- Query folding: check View Native Query. A maximum over a database column can fold, but a row lookup often does not.
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.