List.Sort Function in Power Query M (5 Easy Examples)

List.Sort returns a list whose items are arranged in order, ascending by default. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to put the items in a list in order, by their natural value or by a rule you choose, this is the function you reach for.

Table of Contents

Syntax of List.Sort Function

List.Sort(list as list, optional comparisonCriteria as any) as list

where

list (required, list). The list whose items you want to sort.
comparisonCriteria (optional, any). How to order the items. Omit it for the default ascending sort. It accepts four forms:

– An Order enum value, either Order.Ascending or Order.Descending, to set the direction. – A key function like each Text.Length(_), which returns the value to sort each item by. – A {key, order} list, which sorts by a key and sets the direction at the same time, for example {each Text.Length(_), Order.Descending}. – A two-argument Value.Compare style function that takes two items and returns -1, 0, or 1 for full control over the order.

Returns: a new list with the same items arranged in order. The original list is left unchanged.

In plain terms, you hand it a list, optionally tell it how to order things, and it gives you the sorted list back.

Example 1: Sort a list of numbers

Put a list of numbers in order with no extra arguments.

let
    Source = List.Sort({40, 12, 87, 5, 63})
in
    Source

Result: 5, 12, 40, 63, 87

With no comparisonCriteria, the numbers come back smallest to largest.

Example 2: Sort in descending order

Pass Order.Descending to flip the direction.

let
    Source = List.Sort({40, 12, 87, 5, 63}, Order.Descending)
in
    Source

Result: 87, 63, 40, 12, 5

The same numbers now come back largest to smallest.

Example 3: Sort by a key function

Sort fruit names by how long each word is instead of alphabetically.

let
    Source = List.Sort({"Banana", "Fig", "Cherry", "Kiwi"}, each Text.Length(_))
in
    Source

Result: Fig, Kiwi, Banana, Cherry

Fig (3) comes first, then Kiwi (4). Banana and Cherry are both 6 letters, so they stay in their original order.

Example 4: Sort a single table column

Often you want to sort the values from one column of a table.

Here is the starting data:

City	Population
Denver	715522
Austin	961855
Seattle	737015
Boston	654776

Pull the Population column into a list, then sort it largest first.

let
    Source = Excel.CurrentWorkbook(){[Name="Cities"]}[Content],
    Pop = Source[Population],
    Sorted = List.Sort(Pop, Order.Descending)
in
    Sorted

Result: 961855, 737015, 715522, 654776

Source[Population] turns the column into a list, and List.Sort orders those values on their own. Once sorted descending, the first item is also the maximum value in the column.

Example 5: Sort by a key and a direction together

Use a {key, order} list to sort by word length but with the longest first.

let
    Source = List.Sort({"Banana", "Fig", "Cherry", "Kiwi"}, {each Text.Length(_), Order.Descending})
in
    Source

Result: Banana, Cherry, Kiwi, Fig

The two 6-letter words come first (in input order), then Kiwi (4), then Fig (3).

Things to keep in mind with List.Sort

It is a stable sort. Ties usually keep their original input order. Items with equal keys typically stay in the order they came in, which is why Banana stays before Cherry in the by-length examples. Note that Power Query does not formally guarantee a stable sort, so on larger or folded data ties can occasionally reorder. If stable ordering matters, add an explicit tiebreaker key.
A key function changes what you sort by, not the direction. each Text.Length(_) sorts by length but still ascending. To sort by a key in reverse, use the {key, order} list form (Example 5).
Mixed types throw an error. Sorting {1, "a", 3} raises Expression.Error: We cannot apply operator < to types Number and Text. Keep the list to a single type, or supply a key that returns a comparable value.
null sorts first in ascending order. A null in the list lands at the top of an ascending sort and at the bottom of a descending one.

Common questions about List.Sort

What is the difference between List.Sort and Table.Sort?

List.Sort orders the items in a standalone list, while Table.Sort reorders the rows of a table by one or more columns. Use Table.Sort when you want to keep the whole table together and just change row order.

Can I sort by more than one key?

Yes. Pass a list of {key, order} pairs, for example {{each [LastName], Order.Ascending}, {each [Age], Order.Descending}}, and List.Sort applies them in turn.

List of All Power Query Functions

Related Power Query Functions / Articles: