Forum Discussion

ddpl's avatar
ddpl
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

How List.Sort work???

I want to understand how below M Code work, more explicitly.   = List.Sort( { "Feb", "Jan", "Feb", "Jan", "Mar" }, ( x, y ) => Value.Compare( ...
Need Help
Tutorial Requests
  • ddpl's avatar
    ddpl
    3 years ago

    Anonymous ,

     

    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.

Rickmaurinus's avatar
Rickmaurinus
Icon for Helper V rankHelper V
3 years ago

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.