M Query Guide

Transformations

M Sorting Rows

Sorting Table Rows

M sorting rows uses Table.Sort with column orders.

Introduction to Sorting Rows in M

Sorting rows in the M language is a common data transformation task. M provides the Table.Sort function, which allows you to order rows based on one or more columns. This is particularly useful when you need to organize data for analysis or reporting purposes.

Understanding Table.Sort

The Table.Sort function in M takes two primary arguments: the table to be sorted and a list of column orders that dictate the sorting criteria. Each column order is specified as a record, allowing you to determine the column and the order (ascending or descending).

Basic Usage of Table.Sort

To sort a table by a single column in ascending order, you can use the following basic syntax:

In this example, myTable is the table you want to sort, and ColumnName is the column by which you want to sort the table.

Sorting by Multiple Columns

Often, you might need to sort data based on multiple columns, especially when there are ties in the values of the first column. To do this, you can specify additional columns with their respective sort orders:

This code will first sort the rows by FirstColumn in ascending order. If there are any ties, those rows will be sorted by SecondColumn in descending order.

Custom Sorting Logic

M's Table.Sort also supports custom sorting logic through comparator functions. This allows you to define complex sorting criteria beyond simple ascending or descending orders.

In this example, a custom comparator function is used to sort the ColumnName. You can tailor this function to implement intricate sorting rules specific to your data needs.

Handling Null Values

When sorting, you may encounter null values in your data. M allows you to control how these are handled during sorting. Typically, null values are placed at the end, but you can customize this behavior within your sorting logic.

Transformations

Next
Grouping Rows