List.Sort

Updated on
Rick de Groot

List.Sort is a Power Query M function that sorts a list of data according to optional criteria specified, such as order, key selection, or a custom comparison function. The function returns a sorted list based on the specified criteria.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Sort(
   list as list,
   optional comparisonCriteria as any,
) as list
ArgumentAttributeDescription
ListThe list of data to sort.
ComparisonCriteriaOptional The Order.Type dictates the order in which elements are arranged. When omitting the argument, the function uses Order.Ascending for sorting in ascending order (from smallest to largest or A to Z). You can use Order.Descending for sorting in descending order (from largest to smallest or Z to A).

Description

Sorts a list of data, list, according to the optional criteria specified. An optional parameter, comparisonCriteria, can be specified as the comparison criterion. This can take the following values:

  • To control the order, the comparison criterion can be an Order enum value. (Order.Descending, Order.Ascending).
  • To compute a key to be used for sorting, a function of 1 argument can be used.
  • To both select a key and control order, comparison criterion can be a list containing the key and order ({each 1 / _, Order.Descending}).
  • To completely control the comparison, a function of 2 arguments can be used. This function will be passed two items from the list (any two items, in any order). The function should return one of the following values:
    • -1: The first item is less than the second item.
    • 0: The items are equal.
    • 1: The first item is greater than the second item.
    Value.Compare is a method that can be used to delegate this logic.

Examples

Sorting numbers happens in an ascending order by default. That works for numbers, text, dates etc. 

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

= List.Sort( { "Fish", "Duck", "Ape" } ) // Returns { "Ape", "Duck", "Fish" } 

= List.Sort( {"a", "B", "c", "D" } )     // Returns {"B", "D", "a", "c" } ) 

// Returns #date( 2023, 1, 1), #date( 2023, 1, 3), #date( 2023, 1, 5) 
= List.Sort( { #date( 2023, 1, 3),
               #date( 2023, 1, 5),
               #date( 2023, 1, 1) } )

You can also provide a sort order more explicitly in the second argument. 

= 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" }

This second argument can also be of your own design. 

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

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

Combine multiple sorting criteria by providing them within curly brackets. 

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

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

Finally, you can also provide a custom function as second argument. 

// Returns { "January", "February", "March", "April" }
= List.Sort( { "February", "March", "January", "April" },
             (a, b) => Value.Compare(
                          Date.From(  a & "1900" ),
                          Date.From(  b & "1900" ) ) )


// 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 ) ) )

Learn more about List.Sort in the following articles:

Other functions related to List.Sort are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-sort

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy