Downloads
Power Query MTable: rowsBeginner

Table.Sort

Puts the rows in order by one or more columns, ascending or descending.

Syntax
Table.Sort(table as table, comparisonCriteria as any) as table

Returns: 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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics