Table.Max
The row with the highest value of a column, or of anything worked out from a row, as a record.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Max(table as table, comparisonCriteria as any, optional default as any) as any| Parameter | Type | Description |
|---|---|---|
table | table | The rows to look through. |
comparisonCriteria | any | A column name; a pair {name, Order.Descending}; a list of names or pairs; or a function of a row that gives the value to compare. |
default | any optional | What comes back when the table has no rows; null when left out. |
Returns
any. The winning row as a record; the default when the table is empty.
Remarks
- One row comes back, with all of its columns. For the top few rows use
Table.MaxN; for the highest value alone,List.Maxon the column is shorter.
Examples
The most expensive product
let
Products = #table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}, {"Lamp", 30}})
in
Table.Max(Products, "Price")[Product = "Desk", Price = 120]Highest by a value worked out per row
Revenue is price times units: the function gives the value each row is compared by.
let
Lines = #table({"Product", "Price", "Units"}, {{"Chair", 50, 4}, {"Desk", 120, 1}, {"Lamp", 30, 6}})
in
Table.Max(Lines, each [Price] * [Units])[Product = "Chair", Price = 50, Units = 4]Chair brings 200, Lamp 180 and Desk 120.
A default for an empty table
Table.Max(#table({"Product", "Price"}, {}), "Price", [Product = "none", Price = 0])[Product = "none", Price = 0]