Mastering List.Sort in Power Query M: Comprehensive Guide

Are you tired of struggling to sort your data in Power Query? The List.Sort function offers a solution for all your sorting needs. Not only can you sort your list in ascending or descending order, but you can also provide a custom sort order using the comparison criteria parameter.

This article delves into the possibilities of the List.Sort function, showing you how to take your sorting game to the next level, beyond basic sorting and into advanced requirements.

Let’s start with the basics.

1. Understanding the List.Sort Function

List.Sort is a function that has two arguments. It’s syntax looks as follows:

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

The List.Sort function sorts your values in Ascending order by default. It takes any value it can order. This means it doesn’t matter whether you provide text, numbers, dates, etc. Some examples are:

// To sort a list of numbers in ascending order
= List.Sort( { 1, 3, 2 }  )               // Returns { 1, 2, 3 } 
 
// To sort a list of text values in alphabetical order
= List.Sort( { "Fish", "Duck", "Ape" } ) // Returns { "Ape", "Duck", "Fish" } 
 
// When sorting, uppercase letters are treated as greater than lowercase
= List.Sort( {"a", "B", "c", "D" } )     //  {"B", "D", "a", "c" } ) 
 
// To sort a list of dates in chronological order
= List.Sort( { #date( 2023, 1, 3),
               #date( 2023, 1, 5),
               #date( 2023, 1, 1) } )
// #date( 2023, 1, 1), #date( 2023, 1, 3), #date( 2023, 1, 5) 

These examples sort in Ascending or Alphabetical order by default. There may be cases where the default sorting order does not fulfill your needs. In those cases you can resort to the optional second argument, the comparisonCriteria. So how does the comparisonCriteria argument of List.Sort work?

2. Comparison Criteria Parameter

The comparisonCriteria is an optional argument for the List.Sort function. It lets you to specify a comparison criterion. To specify this you can provide a sorting order with one of four options. You can:

1. provide the Order Types Order.Ascending or Order.Descending to set the relevant sort order.
2. sort your data with a Custom Key.
3. provide both a Custom Key and a Order Type.
4. Provide a custom function of 2 arguments to have complete control over the order of your data.

Let’s delve right in.

2.1. Sort Order

The easiest way to use the Comparison Criteria of List.Sort is by using the Order Types Order.Ascending and Order.Descending. These values specify the order of sorting. Simply put the text as second argument and your sorting order will change.

= List.Sort( { "a", "d", "b", "c" } )                   // { "a", "b", "c", "d" }
= List.Sort( { "a", "d", "b", "c" }, Order.Ascending )  // { "a", "b", "c", "d" }
= List.Sort( { "a", "d", "b", "c" }, Order.Descending ) // { "d", "c", "b", "a" }

2.2. Custom Key

Another way to change the sort order is by using a single argument custom key. You can add a statement that starts with the word each and performs a transformation/calculation on the underlying value referred to by the underscore.

= List.Sort( { 1, 2, 3 }, each 1 / _ )                          // {3, 2, 1}
= List.Sort( {"Ram", "Dog", "Cow"} )                          // {"Cow", "Dog", "Ram"} 
= List.Sort( {"Ram", "Dog", "Cow"}, each Text.End( _, 1 ))    // {"Dog", "Ram", "Cow"}
= List.Sort( {"Ram", "Dog", "Cow"}, each Text.Middle( _, 1 )) // {"Ram", "Dog", "Cow"}

You may sometimes have an issue sorting numbers that are formatted as text. By default the sorting order sorts in an Alphabetical order. That means that “11” comes before “22” because the first character “1” comes earlier than “2”.

= List.Sort( { "1", "11", "2", "123" } ) // Returns { "1", "11", "123", "2" }

= List.Sort( { "1", "11", "2", "123" }, each Number.From( _ ) ) 
// Returns { "1", "2", "11", "123" }

You can get creative here and also use other types of functions. For example, you can use dates here too.

// To sort a list of dates in chronological order
= List.Sort( { #date( 2023, 1, 6),
               #date( 2023, 2, 5),
               #date( 2023, 5, 1) } )
// #date( 2023, 1, 6), #date( 2023, 2, 5), #date( 2023, 5, 1)

// To sort a list of dates by day number
= List.Sort( { #date( 2023, 1, 6),
               #date( 2023, 2, 5),
               #date( 2023, 5, 1) }, 
             each Date.Day( _ ) )
// #date( 2023, 5, 1), #date( 2023, 2, 5), #date( 2023, 1, 6)

// To sort a list of dates by month number
= List.Sort( { #date( 2023, 1, 6),
               #date( 2023, 2, 5),
               #date( 2023, 5, 1) }, 
             each Date.Month( _ ) )
// #date( 2023, 1, 6), #date( 2023, 2, 5), #date( 2023, 5, 1)

And if you want to manually sort days or month names, no problem at all.

= List.Sort( { "February", "March", "January" }, 
      each List.PositionOf( { "January", "February", "March" }, _ ) )
// Returns { "January", "February", "March" }

= List.Sort( { "Wednesday", "Tuesday", "Monday" }, 
      each List.PositionOf( { "Monday", "Tuesday", "Wednesday" }, _ ) )
// Returns { "Monday", "Tuesday", "Wednesday" }

As you can see you can combine all kinds of functions. Whether it’s text, date or numbers, they all work.

We have now looked at a custom key as second argument. You can also combine this key with the Order Types discussed earlier as the next section will show.

2.3. Custom Key and Sort Order

You can use a custom key in combination with the Order Type. This lets you use a custom key like in previous step, but also sorts your data in the desired direction. This is only useful when you want to order your data descending. After all, List.Sort sorts the custom key Ascending by default. To provide both a Custom Key and a Order Type, you should provide both within a list.

Here are some examples:

= List.Sort({ 1, 2, 3 } )                                   // Returns { 1, 2, 3 }
= List.Sort({ 1, 2, 3 }, each 1 / _ )                       // Returns { 3, 2, 1 }
= List.Sort({ 1, 2, 3 }, { each 1 / _, Order.Descending } ) // Returns { 1, 2, 3 }

// To sort a list of dates by day number in descending order
= List.Sort( { #date( 2023, 1, 6),
               #date( 2023, 2, 5),
               #date( 2023, 5, 1) }, 
             { each Date.Day( _ ), Order.Descending } )
// #date( 2023, 1, 6), #date( 2023, 2, 5), #date( 2023, 5, 1)

Now the above example shows how to create a custom key and sort order for a single layer. But let’s say you want to sort your data on multiple expressions.

= List.Sort( { #date( 2023, 1, 1 ), #date( 2023, 2, 1 ),
               #date( 2023, 5, 1 ), #date( 2023, 2, 8) },
             { each Date.Month( _ ), Order.Ascending } )
// Returns dates in the ascending order of their month number
//  {#date( 2023, 1, 1), #date( 2023, 2, 1), #date( 2023, 2, 8), #date( 2023, 5, 1)}

// For clarity:  { 1 Jan 2023, 1 Feb 2023, 8 Feb 2023, 1 May 2023 }

You can also add an additional layer of sorting. Who knows you might want to first sort by Month Name and secondly by Date in a Descending way.

= List.Sort( { #date( 2023, 1, 1),
               #date( 2023, 2, 1),
               #date( 2023, 5, 1),
               #date( 2023, 2, 8) },
{{ each Date.Month( _ ), Order.Ascending}, {each _, Order.Descending} } )
// Returns dates in the ascending order of their month number and DESCENDING date
// {#date( 2023, 1, 1), #date( 2023, 2, 8), #date( 2023, 2, 1), #date( 2023, 5, 1)}

// For Clarity { 1 Jan 2023, 8 Feb 2023, 1 Feb 2023,  1 May 2023 }

2.4. Custom Function

As last option, you can have complete control over the sort order by using a 2 argument function. To arrive at a sort order, this function is passed onto two items from the list. This can be any two items. The function should then return:

• -1: The first item is smaller / earlier in the alphabet than the second.
• 0: Both items are of equal value.
• 1: The first item is greater / later in the alphabet than the second.

For example, in the case of months you could use:

= List.Sort( { "February", "March", "January", "April" }, 
          (a, b) => 
             Value.Compare( 
               List.PositionOf( { "January", "February", "March", "April" }, a),
               List.PositionOf( { "January", "February", "March", "April" } , b ) ) )

So what do the letters a and b mean? These are the parameters of our function. In this case what happens is that the function compares a value in the list (a) with a different value in that same list (b). We could have named this with x and y or base and comparison. It’s simply a variable that we can reference.

Behind the scenes what the function does is, it compares each of the values and puts them in the right order. The only important thing to remember is that the function will take each value and tries to find its place in the list provided to List.PositionOf. The order in which it tries that is irrelevant, each value will be evaluated.

If you see the Value.Compare function for the first time here, use the next section to get familiar with how it works.

2.4.1. Understanding Value.Compare

So what does the Value.Compare function do? The Value.Compare function compares two values. And it checks whether the first value is less than, equal to or greater than the second value.

To illustrate this, have a look at the below three statements. In case the first value is smaller than the second one it returns -1. When they’re equal it returns 0 and 1 when it’s greater than the other value.

= Value.Compare( 3, 5 ) // Returns -1
= Value.Compare( 5, 5 ) // Returns 0
= Value.Compare( 5, 3 ) // Returns 1

Just imagine the function does something like:

= let 
     a = 5, 
     b = 3, 
     fxValueCompare = if a < b then -1 else if a = b then 0 else 1
  in
fxValueCompare

The Value.Compare function works with numbers, text, dates, logicals, datetime, and any other value that can be compared. Note that null values throw an error. Comparing any type to be bigger or smaller than null results in an error. So what work is the following:

= Value.Compare( 3, 5 )               // -1
= Value.Compare( 5, 5 )               // 0
= Value.Compare( 5, 3 )               // 1
 
= Value.Compare( "Apple", "Banana" )  // -1
= Value.Compare( "Banana", "Banana" ) // 0
= Value.Compare( "Banana", "Apple" )  // 1
 
= Value.Compare( #date( 2020, 1, 1 ), #date( 2020, 1, 2 ) ) // -1
= Value.Compare( #date( 2020, 1, 1 ), #date( 2020, 1, 1 ) ) // 0
= Value.Compare( #date( 2020, 1, 2 ), #date( 2020, 1, 1 ) ) // 1
 
= Value.Compare( #datetime(2023,2,2,10,10,10), #datetime(2023,2,1,10,10,10)  ) // 1
= Value.Compare( #datetime(2023,2,1,10,10,10), #datetime(2023,2,1,10,10,10)  ) // 0
= Value.Compare( #datetime(2023,2,1,10,10,10), #datetime(2023,2,2,10,10,10)  ) // -1
 
= Value.Compare( true, false )   // 1
= Value.Compare( true, true )    // 0
= Value.Compare( true, false )   // -1

So in principle it’s a very basic function. You can compare the position of values in relation to one another and verify which one is greater, equal or smaller. How can you incorporate this into List.Sort?

2.4.2. Sort Order: Full Control

There are many cases where an Ascending or Descending sort order is difficult. Below follow some of those cases and how we can solve them.

First let’s have a look at a classic example of sorting Month Names. The alphabetical sort order does not represent the order of how they appear throughout a year.

= List.Sort( { "February", "March", "January", "April" } )
// Returns { "April", "February", "January", "March" }

// A way to get the correct sorting is the following
= List.Sort( { "February", "March", "January", "April" }, 
             (a, b) => 
                Value.Compare( 
                  List.PositionOf( { "January", "February", "March", "April" }, a),
                  List.PositionOf( { "January", "February", "March", "April" } , b )))
// Returns { "January", "February", "March", "April" }

Let’s explore what’s happening in the last example. The Month Name values provided in the first argument are text values. Naturally List.Sort sorts these alphabetically in an ascending way.

To provide the correct sorting order, the above code uses the Value.Compare function. Instead of looking at the sort order within the alphabet, the sorting logic now looks at the position of each value within the provided list. Since we provided the list in order, you now get the desired outcome.

The List.PositionOf function is provided twice within Value.Compare, so that the position of each values can be compared. Remember that the result of Value.Compare is -1, 0 or 1, which is used for putting values in the right order.

Let’s look at some more examples:

// To prevent manual input, you can transform the Month Names into Dates
// Below transforms all to the 1st date of the month for the year 1900. 
= List.Sort( { "February", "March", "January", "April" },
             (a, b) => Value.Compare(
                          Date.From(  a & "1900" ),
                          Date.From(  b & "1900" ) ) )
// Returns { "January", "February", "March", "April" }
 
 
// You can provide Value.Compare with a record that contains the Day of Week numbers
= List.Sort( {"Wednesday", "Monday", "Tuesday" },
                    let LookupRecord = [ Monday = 1, Tuesday = 2, Wednesday = 3] in
             ( a, b ) => Value.Compare(  
                            Record.Field( LookupRecord, a ),
                            Record.Field( LookupRecord, b ) ) )

Now let’s say you have null values in your list. By default null values appear earlier than numbers. Now what can you do if you want null values last?

= List.Sort( { 3, 1, 2, null } ) // Returns { null, 1, 2, 3 }

// To return null last, you can provide the entire list
= List.Sort( { 3, 1, 2, null },  
             ( x , y ) => Value.Compare( 
                                  List.PositionOf( { 1, 2, 3, null }, x ) 
                                , List.PositionOf( { 1, 2, 3, null }, y ) ) 
                                )
// Returns { 1, 2, 3, null }

// You can also provide an if condition that returns the same result
= List.Sort( { 3, 1, 2, null },  
             ( x , y ) => if x = null then 1 else Value.Compare( x, y ) )

3. Summary

In conclusion, the List.Sort function in Power Query is a great tool for sorting your data in a variety of ways. From basic ascending and descending order, to more advanced sorting options like custom keys and custom functions.

As this chapter showed the List.Sort function hides a spectacular amount of flexibility. You will find beginning M coders using helper-columns to provide their sorting. But when you master the techniques provided in this article you become a powerful sorter. You then have the powers to skip the extra steps and define your sort order within the List.Sort function itself.

Do you have any other tips or tricks for using the List.Sort function in Power Query? Share them in the comments below!

Enjoy Power Query!