List.Sort

D

Back to top

T

Back to top

#

Back to top

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

Understanding List.Sort Function

List.Sort is a simple function that can be used to sort lists of values in ascending or descending order. The syntax of the List.Sort function is as follows:

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

Here, the `list` represents the list of values that we want to sort. The `comparisonCriteria` is an optional parameter that we can use to specify the type of comparison to be used while sorting the list. The `sortOrder` is another optional parameter that can be used to specify whether the list should be sorted in ascending or descending order.

Breaking Down the M Code Behind List.Sort

Now let's take a closer look at the M code behind List.Sort. The following is the M code for the List.Sort function:

let

Source = List.Sort(list, comparisonCriteria, sortOrder)

in

Source

This M code is fairly straightforward. It defines a variable called `Source`, which uses the List.Sort function to sort the list. The List.Sort function takes the three parameters that we discussed earlier: `list`, `comparisonCriteria`, and `sortOrder`.

However, the real magic happens inside the List.Sort function. When we call the List.Sort function, it actually calls a hidden function called the comparer function, which is responsible for comparing the values in the list and sorting them.

The Comparer Function

The comparer function is an internal function that is used by List.Sort to compare the values in the list and sort them. The comparer function takes two values as input and returns a value indicating whether the first value is less than, equal to, or greater than the second value.

The following is the M code for the comparer function used by List.Sort:

let

Source = (x as any, y as any) =>

if comparisonCriteria <> null then

Value.Compare(x, y, comparisonCriteria)

else

Value.Compare(x, y)

in

Source

This code defines a variable called `Source`, which is a lambda function that takes two values (`x` and `y`) as input. The lambda function then checks if the `comparisonCriteria` parameter is specified. If it is, then it calls the `Value.Compare` function with the specified `comparisonCriteria`. If it is not specified, then it simply calls the `Value.Compare` function without any parameter.

The `Value.Compare` function is another internal function that is used by List.Sort to compare values. It takes two values as input and returns -1 if the first value is less than the second value, 0 if the two values are equal, and 1 if the first value is greater than the second value.

In this article, we explored the M code behind the List.Sort function in Power Query. We learned that the List.Sort function uses a hidden comparer function to compare values and sort them in ascending or descending order. We also learned that the comparer function uses the Value.Compare function to compare values based on the specified comparison criteria.

Understanding the M code behind Power Query functions is essential for anyone looking to use this tool for data transformation and cleaning. By understanding how List.Sort works behind the scenes, we can use this function more efficiently and effectively in our data analysis tasks.

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)