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

If you want to order the rows of a table by one or more columns in Power Query, Table.Sort is the function that does it. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

You give it a table and the columns to sort on, and it hands back the same rows in the order you asked for.

Table of Contents

Syntax of Table.Sort Function

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

where

table (required, table). The table whose rows you want to reorder.
comparisonCriteria (required, any). The column or columns to sort by, with an optional sort direction for each. The common form is {{"Column", Order.Ascending}}. For a single column you can also pass just {"Column"}.

Returns: a table with the same columns and rows, reordered according to the sort criteria.

In plain terms, you point it at a table and name the columns to sort on, and it returns the rows in that order.

Example 1: Sort a table by one column ascending

You have a list of sales reps and their numbers, and you want them ordered from lowest sales to highest.

Here is the starting data:

Rep	Sales
Bob	30
Ann	90
Cy	60
Dee	45

Sort on the Sales column in ascending order:

let
    Source = Excel.CurrentWorkbook(){[Name="RepSales"]}[Content],
    #"Sorted Rows" = Table.Sort(Source, {{"Sales", Order.Ascending}})
in
    #"Sorted Rows"

The rows come back ordered by Sales, smallest first:

Rep	Sales
Bob	30
Dee	45
Cy	60
Ann	90

Order.Ascending runs low to high, so 30 lands at the top and 90 at the bottom.

Example 2: Sort a table by one column descending

This time you want the highest scores at the top of a quiz results table.

Here is the starting data:

Student	Score
Maya	72
Leo	88
Ivy	65
Sam	91

Sort on the Score column in descending order:

let
    Source = Excel.CurrentWorkbook(){[Name="QuizScores"]}[Content],
    #"Sorted Rows" = Table.Sort(Source, {{"Score", Order.Descending}})
in
    #"Sorted Rows"

The rows come back ordered by Score, highest first:

Student	Score
Sam	91
Leo	88
Maya	72
Ivy	65

Order.Descending runs high to low, so 91 is now at the top.

Example 3: Sort by two columns at once

You have deals across regions, and you want them grouped by Region alphabetically, with the biggest deal first inside each region.

Here is the starting data:

Region	Rep	Amount
East	Tom	200
West	Liz	150
East	Joe	350
West	Kim	400
North	Rex	100

Pass two sort keys, Region ascending then Amount descending:

let
    Source = Excel.CurrentWorkbook(){[Name="RegionDeals"]}[Content],
    #"Sorted Rows" = Table.Sort(Source, {{"Region", Order.Ascending}, {"Amount", Order.Descending}})
in
    #"Sorted Rows"

Rows are grouped by Region first, then ordered by Amount within each region:

Region	Rep	Amount
East	Joe	350
East	Tom	200
North	Rex	100
West	Kim	400
West	Liz	150

The second key only decides order among rows that tie on the first key, so Amount only matters inside each region.

Example 4: Sort text values alphabetically

You have a staff list in no particular order and want the names sorted A to Z.

Here is the starting data:

Name	Dept
Zoe	HR
Alex	IT
Mike	Sales
Bella	IT

Sort on the Name column in ascending order:

let
    Source = Excel.CurrentWorkbook(){[Name="StaffList"]}[Content],
    #"Sorted Rows" = Table.Sort(Source, {{"Name", Order.Ascending}})
in
    #"Sorted Rows"

The names come back in alphabetical order:

Name	Dept
Alex	IT
Bella	IT
Mike	Sales
Zoe	HR

Text sorts alphabetically with ascending order, which is why Alex leads and Zoe trails.

Example 5: Tied rows keep their original order

You have products with units sold, and several rows share the same Units value. You want to see what Table.Sort does with those ties.

Here is the starting data:

Category	Product	Units
Toys	Kite	50
Books	Atlas	30
Toys	Drone	50
Books	Novel	30

Sort on the Units column in descending order:

let
    Source = Excel.CurrentWorkbook(){[Name="ProductUnits"]}[Content],
    #"Sorted Rows" = Table.Sort(Source, {{"Units", Order.Descending}})
in
    #"Sorted Rows"

The 50 rows come first and the 30 rows follow, and within each group the rows stay in their original order:

Category	Product	Units
Toys	Kite	50
Toys	Drone	50
Books	Atlas	30
Books	Novel	30

Table.Sort is a stable sort. Kite stays ahead of Drone and Atlas ahead of Novel because that was their order in the source.

Things to keep in mind with Table.Sort

The default is ascending. If you only name a column with no Order value, it sorts ascending. Add Order.Descending to flip it.
Text sorts are case-insensitive in Excel Power Query. apple and Apple sort together rather than splitting into separate groups.
A misspelled column name errors out. Sorting on a column that does not exist throws Expression.Error: The column 'Saless' of the table wasn't found. Check the name against the source step.

Performance and query folding

Against a foldable source like SQL Server, Table.Sort folds to an ORDER BY clause, so the database does the sorting. On Excel, CSV, or folder sources it runs locally, which is fine for normal table sizes. Sort late in your query, after you have filtered and removed columns, so you are ordering fewer rows.

Common questions about Table.Sort

What is the difference between Table.Sort and the Sort buttons in the Power Query editor?

Nothing functional. Clicking a column header sort just writes a Table.Sort step for you, so editing the formula directly gives you the same result with more control over multiple keys.

Can I sort by something other than a plain column value?

Yes. The criteria can be a function, so Table.Sort(Source, each Text.Length([Name])) sorts rows by the length of Name rather than by the name itself.

List of All Power Query Functions

Related Power Query Functions / Articles: