Table.MaxN
The top rows by a column or by a value worked out per row: a number of them, or as many as meet a condition.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.MaxN(table as table, comparisonCriteria as any, countOrCondition as any) as table| Parameter | Type | Description |
|---|---|---|
table | table | The rows to rank. |
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. |
countOrCondition | any | How many rows to keep, or a condition: rows are taken from the top while it holds. |
Returns
table. The rows from the highest down, with all of the table's columns.
Remarks
- With a condition, the rows are ordered highest first and taken until the first one that fails it.
- A count larger than the table gives every row, highest first.
Examples
The two best shops
let
Shops = #table({"Shop", "Sales"}, {{"Oslo", 120}, {"Bergen", 340}, {"Trondheim", 90}, {"Stavanger", 210}})
in
Table.MaxN(Shops, "Sales", 2)| Shop | Sales |
|---|---|
| Bergen | 340 |
| Stavanger | 210 |
Every shop above 100
let
Shops = #table({"Shop", "Sales"}, {{"Oslo", 120}, {"Bergen", 340}, {"Trondheim", 90}, {"Stavanger", 210}})
in
Table.MaxN(Shops, "Sales", each [Sales] > 100)| Shop | Sales |
|---|---|
| Bergen | 340 |
| Stavanger | 210 |
| Oslo | 120 |