List.Sort
Returns the items of a list in order, smallest to largest by default.
List.Sort(list, optional comparisonCriteria)Returns: A new sorted list. The original list is not changed.
What it does
List.Sort puts the items in order. By default that is ascending (smallest first for numbers, A to Z for text, earliest first for dates). Pass Order.Descending to reverse it. Use it when you need the values in a fixed order, for example to list the largest expenses first or to show months in calendar order.
Syntax and arguments
list: the items to sort.comparisonCriteria(optional):Order.Ascending(the default),Order.Descending, or a function that returns the value to sort by.
Example
A cost controller wants the expense categories for the quarter listed from largest to smallest, starting from a list of amounts.
let
Amounts = {4200, 310, 18750, 980},
Largest = List.Sort(Amounts, Order.Descending)
in
Largest
The result is {18750, 4200, 980, 310}. Sorting a list of plain numbers is simple. To sort a table by a column, use Table.Sort, which keeps each row's other columns together.
Example: a second, different use
Sorting text: List.Sort({"West", "East", "North"}) gives East, North, West.
Things to watch
- Text sorts by character code, so "10" comes before "9", and capital letters may sort before lower case. Pad numbers stored as text with Text.PadStart if you need natural order.
- Nulls sort in a fixed place. Remove them first with List.RemoveNulls if they would spoil the order.
- A sorted list is a new list. Assign it to a name, or the sort is lost.
- Sorting a long list against a database source may not fold. Sort in the source query when possible.
In business terms
Top-ten expense lists, aging buckets in order, months in calendar order and rankings of customers by balance all depend on sorting. Sort the data before a report so the order is not left to chance.