Table.Sort
Puts the rows in order by one or more columns, ascending or descending.
Table.Sort(table as table, comparisonCriteria as any) as tableReturns: The same rows in a new order.
What it does
Table.Sort arranges the rows in the order you ask for. Sorting does not change any values. It only changes the order. Sort by date to build a chronological ledger, by due date to build an aging list, or by amount to find the largest items for review.
Syntax and arguments
- table: the table to sort. Required.
- comparisonCriteria: either a column name, which sorts ascending, or a list of pairs. Each pair is a column and
Order.AscendingorOrder.Descending. With several pairs, the table sorts by the first column, then by the next within each group. Required.
Example
A receivables list is sorted with the oldest due date first. Customers with the same due date are then listed in name order.
let
Source = Excel.CurrentWorkbook(){[Name = "OpenInvoices"]}[Content],
Typed = Table.TransformColumnTypes(Source, {{"DueDate", type date}}),
OldestFirst = Table.Sort(Typed, {{"DueDate", Order.Ascending}, {"Customer", Order.Ascending}})
in
OldestFirst
The due date must be a real date before the sort, otherwise the dates are ordered as text. The result lists the oldest items at the top, ready for an aging review.
Example: a second, different use
The sample payments are sorted from the largest to the smallest, so the biggest receipts are listed first.
let
Source = Sql.Database("learnsql.cpetoday.com,1433", "classicmodels"),
Payments = Source{[Schema = "classicmodels", Item = "payments"]}[Data],
Largest = Table.Sort(Payments, {{"amount", Order.Descending}})
in
Largest
Things to watch
- Sort text dates and numbers only after they are typed. Text sorts character by character, so "10" comes before "9".
- Some steps do not keep the sorted order. Grouping and most joins can reorder the rows, so sort again after them.
- A sort plus a row count, such as Table.FirstN, is a common pattern for top lists.
- Query folding: a sort on database columns usually folds to ORDER BY. Check View Native Query if the sort is followed by a step that does not fold.
In business terms
Aging lists are sorted oldest first, ledgers are sorted by date, and review lists are sorted by size. Sorting in the query means the same order is produced every time the report refreshes.