Table.Sort - Table Function

Table.Sort is a Power Query M function that sorts a table using a list of one or more column names and optional comparisonCriteria. The function returns a sorted table.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
table		The table to sort.
comparisonCriteria		The Order.Type dictates the order in which elements are arranged. When omitting the argument, the function uses Order.Ascending for sorting in ascending order (from smallest to largest or A to Z). You can use Order.Descending for sorting in descending order (from largest to smallest or Z to A). Alternatively, you can provide a custom function for your comparisons

Description

The primary function of Table.Sort is to sort a table’s rows. This sorting can be based on one or more columns, and each column can be sorted in either ascending or descending order. The function is highly flexible, accommodating simple sorting (like sorting a list of names alphabetically) to more complex scenarios (like sorting by one column in ascending order and another in descending order).

The function also support custom functions for your comparisonCriteria. You can provide a comparison criteria in multiple ways:

Basic Sorting: you can use the form { { col1, comparisonCriteria }, {col2, comparisonCriteria} }
Custom Sorting: uses the form { customCriteria, { col1, comparisonCriteria } }

Examples

Consider a basic table structure in Power Query M. To illustrate, let’s focus on a table designed for sorting purposes:

Table.Sort base table for sorting in Power Query M

Sorting by a Single Column

In this scenario, if you wish to organize the data by the ‘Item’ column, you would use the Table.Sort function. The code to achieve an ascending sort on the ‘Item’ column, you can use the Order.Ascending enumeration as follows:

Table.Sort(
  Source,
  { { "Item", Order.Ascending } }
)

Sort table in Ascending using Table.Sort in Power Query M

This code effectively arranges the table rows in ascending order based on the ‘Item’ column values.

Sorting by Multiple Columns

Table.Sortalso allows for more complex sorting involving multiple columns. Building on the previous example where we sorted by ‘Item’, suppose you now want to add a secondary sorting condition. For instance, after sorting ‘Item’ in ascending order, you might want to sort the ‘Aisle’ column in descending order. This can be accomplished by adding an additional sorting rule:

Table.Sort(
  Source,
  { {"Item", Order.Ascending}, {"Aisle", Order.Descending} } 
)

Table.Sort using multiple columns for sorting in Power Query M

This code first sorts the table by ‘Item’ in ascending order and then sorts by ‘Aisle’ in descending order.

Custom Sorting Conditions

There are instances where standard sorting might not meet your needs, and you require a custom sorting logic. For example, you might want to sort the ‘Item’ column so specific values (e.g., Apple, Pickle, Banana) appear in a predefined order, and then apply an ascending sort on the ‘Aisle’ column. This level of customization is possible using a custom comparisonCriteria as shown below:

 Table.Sort( 
  Source,
  { each List.PositionOf( {"Apple", "Pickle", "Banana"}, [Item] ), 
    { "Aisle",Order.Ascending } } 
)

Table.Sort custom sort order in Power Query M

In this case, the table is first sorted by a custom order defined for the ‘Item’ column and then by the ‘Aisle’ column in ascending order.

To try the code at home, you can paste the following code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIsKMhJVYrVgfCcEvOAEM4NyEzOhso6oah1QlIbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Aisle = _t, Item = _t]),
    SortAscending = Table.Sort(Source,{{"Item", Order.Ascending}}),
    MultiColumnSort = Table.Sort(Source,{{"Item", Order.Ascending}, {"Aisle", Order.Descending}}),
    CustomCondition = Table.Sort( Source,{ each List.PositionOf({"Apple", "Pickle", "Banana"}, [Item]) , {"Aisle",Order.Ascending}})
in
    CustomCondition

Learn more about Table.Sort in the following articles:

Removing Duplicates in Power Query (Complete Guide)
Removing duplicates in Power Query – This guide explores basic and advanced techniques and why you may be getting inconsistent results. » Read more

Other functions related to Table.Sort are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-sort