How List.Sort work???

[ddpl]

I want to understand how below M Code work, more explicitly.

= List.Sort( { "Feb", "Jan", "Feb", "Jan", "Mar" }, 
             ( x, y ) => Value.Compare( 
                                        List.PositionOf( { "Jan","Feb","Mar" }, x ), 
                                        List.PositionOf( { "Jan","Feb","Mar" }, y ) ) ) 

// Returns { "Jan", "Jan", "Feb", "Feb", "Mar" }

If anyone can explain, it will be very helpful.

Thanks.

[Rickmaurinus]

That's a good question. I just wrote a blogpost on how the List.Sort comparison criteria works. It often involves Value.Compare, but you can also combine it with if statements. Some examples that work: 

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

but also know you can use other set-ups like: 

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

There's another awesome one using a record to lookup the order. You can find it on my blog: 

How List.Sort works in Power Query - BI Gorilla

Hope that helps 🙂

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Daryl-Lynch-Bzy]

@ddpl - interesting questions.  @RickdeGroot - could you expand your Gorilla BI List example to offer a better explanation of the mechanics?

This is the mechanics of the optional "comparisonCriteria" parameter when you do not want to follow the "a-z, A_Z, 0-9" or descending order. 

I think the basic idea is that "Jan" is found in position 1 and compared to "Jan" which is also in position 1, so it is returned in non-alphabetic order of 1, which is less than 2 and 3); then Feb is in position 2  as it greater than 1 and less than 3 etc. 

However, this only works if the original item is included in these position of list and the position of order matches.  Here are a couple of example to view in Power Query to understand the behaviour and the position of the items using numbers rather than text.  Notice how the sort only applies to items defined in the PositonOf statement.  If the original items do not appear in the sorting list, these are return first in their original unsorted order.

let
    list = { "Feb", "Jan", "Feb", "Jan", "Mar" },
    list_az = List.Sort( list, Order.Ascending),
    list_za = List.Sort( list, Order.Descending),
    list_example = List.Sort( list , 
             ( x , y ) => Value.Compare( 
                                  List.PositionOf( { "Jan","Feb","Mar" }, x ) 
                                , List.PositionOf( { "Jan","Feb","Mar" }, y ) ) 
                                ),
    //How this appears using numbers adding three new items to the list                              
    numbers = { 2, 6, 1, 4, 2, 1, 4, 5, 3 },
    numbers_az = List.Sort( numbers , Order.Ascending ),
    numbers_za = List.Sort( numbers , Order.Descending ),
    // https://learn.microsoft.com/en-us/powerquery-m/list-sort.  This is relative position ascending order sort because 1/6th is smaller than 1/5th , 1/4th etc
    number_doc_example = List.Sort(numbers, (x, y) => Value.Compare(1/x, 1/y)),
    // The position of the item and the contents of the list are important to sorting.  If the item is undefined it appear first in unsorted order.
    number_example_1 = List.Sort( numbers , 
             ( x , y ) => Value.Compare( 
                                  List.PositionOf( { 2, 3, 1 }, x ) 
                                , List.PositionOf( { 2, 3, 1 }, y ) ) 
                                ),
    number_example_2 = List.Sort( numbers , 
             ( x , y ) => Value.Compare( 
                                  List.PositionOf( { 3, 1, 2 }, x ) 
                                , List.PositionOf( { 3, 1, 2 }, y ) ) 
                                ),
    //4 is now included in the complex sort, but 6 and 5 remain at start in appearence order
    number_example_3 = List.Sort( numbers , 
             ( x , y ) => Value.Compare( 
                                  List.PositionOf( { 3, 1, 2, 4 }, x ) 
                                , List.PositionOf( { 3, 1, 2, 4 }, y ) ) 
                                ),
    //no real order provided, so the sort is only applied to 6 and 4 based on position, they remaining in order of apperance, but after the undefined items.
    number_broken_order = List.Sort( numbers , 
             ( x , y ) => Value.Compare( 
                                  List.PositionOf( { 3, 2, 1 }, x ) 
                                , List.PositionOf( { 1, 2, 3 }, y ) ) 
                                )

in
     number_broken_order

[ddpl · Accepted solution]

@Daryl-Lynch-Bzy ,

Your explaination did explaine alot about the behaviour of List.Sort, Thanks for that.

I want to ask further that below M-Codes give me exact same result...

= List.Sort( { "Feb", "Jan", "Feb", "Jan", "Mar" }, 
             ( x, y ) => Value.Compare( 
                                        List.PositionOf( { "Jan","Feb","Mar" }, x ), 
                                        List.PositionOf( { "Jan","Feb","Mar" }, y ) ) )

= List.Sort( { "Feb", "Jan", "Feb", "Jan", "Mar" }, 
             ( x, y ) => Value.Compare( 
                                        List.PositionOf( { "Jan","Feb","Mar" }, x ), 
                                        List.PositionOf( { "Mar","Feb","Jan" }, y ) ) )

= List.Sort( { "Feb", "Jan", "Feb", "Jan", "Mar" }, 
             ( x, y ) => Value.Compare( 
                                        List.PositionOf( { "Jan","Feb","Mar" }, x ), 
                                        List.PositionOf( { "Mar","Jan","Feb" }, y ) ) )

Result is... { "Jan", "Jan", "Feb", "Feb", "Mar" } for all 3 of them.

I want to know that if position/order of list items inside of List.PositionOf statment did not match, how List.Sort behave...?

Hope it makes sense.