Table.Sort

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.Sort

Table.Sort is a function that sorts a table based on one or more columns. In this article, we will explore the M code behind Table.Sort and how you can use it to sort your data.

Understanding Table.Sort

Table.Sort takes two arguments: the table you want to sort and a list of columns and sort direction. You can use the optional third argument to specify a custom sorting function.

Here is the basic syntax of Table.Sort:

Table.Sort(table as table, sortColumns as list, optional comparer as nullable function) as table

Let’s explore each argument in more detail:

table

The table argument is the table you want to sort. It can be any table in your data model. You can reference a table by its name, like this:

Table.Sort(MyTable, {{"Column1", Order.Ascending}})

sortColumns

The sortColumns argument is a list of columns and sort directions. You can specify one or more columns to sort by, in the order you want. Each column is represented by a list of two elements: the column name and the sort direction (either Order.Ascending or Order.Descending).

Table.Sort(MyTable, {{"Column1", Order.Ascending}, {"Column2", Order.Descending}})

This will sort MyTable by Column1 in ascending order, and then by Column2 in descending order.

comparer

The comparer argument is an optional custom sorting function. If you don’t specify this argument, Table.Sort will use the default sorting function for each column.

You can use a custom sorting function if you want to sort the data in a specific way that is not supported by the default sorting function. For example, you can use a custom sorting function to sort dates in a specific format.

Table.Sort(MyTable, {{"DateColumn", Order.Ascending}}, (a, b) => Date.Compare(Date.FromText(a), Date.FromText(b)))

This will sort MyTable by DateColumn in ascending order, using a custom sorting function that converts the text representation of the date into a Date value before comparing.

Using Table.Sort in Practice

Now that we understand how Table.Sort works, let’s explore some practical examples of how you can use it in your data transformations.

Sorting by a Single Column

The simplest use case for Table.Sort is to sort a table by a single column. Here is an example:

let

Source = Table.FromRows({{"John", 35}, {"Jane", 28}, {"Bob", 42}}, {"Name", "Age"}),

SortedTable = Table.Sort(Source, {{"Age", Order.Ascending}})

in

SortedTable

This will sort the Source table by the Age column in ascending order. The resulting table will be:

Name Age

Jane 28

John 35

Bob 42

Sorting by Multiple Columns

You can also sort a table by multiple columns. Here is an example:

let

Source = Table.FromRows({{"John", "Doe", 35}, {"Jane", "Doe", 28}, {"Bob", "Smith", 42}, {"Alice", "Smith", 23}}, {"First Name", "Last Name", "Age"}),

SortedTable = Table.Sort(Source, {{"Last Name", Order.Ascending}, {"First Name", Order.Ascending}})

in

SortedTable

This will sort the Source table by the Last Name column in ascending order, and then by the First Name column in ascending order. The resulting table will be:

First Name Last Name Age

Bob Smith 42

Alice Smith 23

John Doe 35

Jane Doe 28

Sorting by a Custom Function

Finally, you can sort a table by a custom function. Here is an example:

let

Source = Table.FromRows({{"John", "Doe", "01/01/1980"}, {"Jane", "Doe", "12/01/1990"}, {"Bob", "Smith", "05/01/1975"}}, {"First Name", "Last Name", "Date of Birth"}),

SortedTable = Table.Sort(Source, {{"Date of Birth", Order.Ascending}}, (a, b) => Date.Compare(Date.FromText(a, "MM/dd/yyyy"), Date.FromText(b, "MM/dd/yyyy")))

in

SortedTable

This will sort the Source table by the Date of Birth column in ascending order, using a custom function that converts the text representation of the date into a Date value before comparing. The resulting table will be:

First Name Last Name Date of Birth

Bob Smith 05/01/1975

John Doe 01/01/1980

Jane Doe 12/01/1990

Table.Sort is a powerful function that allows you to sort your data in Power Query. By understanding the M code behind Table.Sort, you can create custom sorting functions and manipulate your data in any way you want. With these skills, you can take your data transformations to the next level and solve complex problems with ease.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)