Table.Sort
Puts the rows in order by one or more columns, each ascending or descending.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Sort(table as table, comparisonCriteria as any) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to order. |
comparisonCriteria | any | A column name; a pair {name, Order.Descending}; a list of names or pairs, the first deciding first; or a function of a row that gives the value to order by. |
Returns
table. The same rows in the new order.
Remarks
- Ascending is the default. Nulls come first going up and last going down.
- Text is ordered by character code: capitals come before small letters, so "Desk" sorts before "apple".
Examples
By shop, then the best sellers first
let
Sales = #table({"Shop", "Product", "Sales"}, {{"Oslo", "Desk", 120}, {"Bergen", "Chair", 50}, {"Oslo", "Chair", 80}})
in
Table.Sort(Sales, {{"Shop", Order.Ascending}, {"Sales", Order.Descending}})| Shop | Product | Sales |
|---|---|---|
| Bergen | Chair | 50 |
| Oslo | Desk | 120 |
| Oslo | Chair | 80 |
Where the nulls go
Table.Sort(#table({"Product", "Price"}, {{"Chair", 50}, {"Desk", null}, {"Lamp", 20}}), "Price")| Product | Price |
|---|---|
| Desk | null |
| Lamp | 20 |
| Chair | 50 |
By a value worked out per row
Table.Sort(#table({"Product"}, {{"Bookcase"}, {"Desk"}, {"Lamp shade"}}), each Text.Length([Product]))| Product |
|---|
| Desk |
| Bookcase |
| Lamp shade |
Shortest name first.
Related functions
Table.ReverseRows, Table.Max, Table.AddRankColumn, List.Sort.