question

MiguelCaballeroSierra-6578 avatar image
0 Votes"
MiguelCaballeroSierra-6578 asked Ehren commented

I want to understand Comparison Criteria: completely control the comparison, like: List.Sort({2, 3, 1}, (x, y) => Value.Compare(1/x, 1/y))

I have been studying the Comparison Criteria in List functionsin in M languague, but the last one at the bottom on this page: https://docs.microsoft.com/en-us/powerquery-m/list-functions , says:

“To completely control the comparison, a function of 2 arguments can be used that returns -1, 0, or 1 given the relationship between the left and right inputs. Value.Compare is a method that can be used to delegate this logic”

The example in List.Sort is:

 c0 =
 List.Sort ( 
     { 2, 3, 1 },
     ( x, y) => Value.Compare ( 1/x, 1/y )
 )

// Witch return: { 3, 2, 1 }

But later I create this case:

 c1 = 
 List.Sort (
     { -2, -1, 0, 1, 2 },
     ( x, y ) =>
     Value.Compare ( 
         Number.Abs ( x ),
         Number.Acos ( y )
      )
 )

// Witch return: { -2, 1, 0, 2, -1 }

Bit if I change the order of the input, like:

 c2 = 
 List.Sort (
     { 2, -1, 1, 0, -2 },
     ( x, y ) =>
     Value.Compare ( 
         Number.Abs ( x ),
         Number.Acos ( y )
      )
 )

// The result is: { 2, 1, 0, -2, -1 }

Maybe the example with Number.Abs and Number.Acos is to rare, but what about this case:

 c2 =
 List.Sort (
     { 2, 7, -2, 4 },
     ( a, b ) =>        
     Value.Compare (
         -a,
         Number.Power (b, 2 )
     )
 )

// Result: { 4, -2, 7, 2 }

I really would like to understand
Thanks a lot
- Miguel Caballero






power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ehren avatar image
0 Votes"
Ehren answered Ehren commented

Your comparer in the latter examples is asymmetric. Calling yourComparer(val1, val2) should equal -yourComparer(val2, val1).

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Ehren ,

Thanks for the answer, Can you please give me more information how this parameter: ComparisonCriteria works in the form of a function with take two arguments

I mean:

List.Sort ( {3,2,1}, (a, b) => Value.Compare (1/a, 1/b) )

But what value take the parameter a, and what value take the parameter b

Thanks
- Miguel
I

0 Votes 0 ·
Ehren avatar image Ehren MiguelCaballeroSierra-6578 ·

The comparison criteria function will be passed two items from the list. It can be any two items, in any order. The function should return one of the following values:

  • -1: The first item should be placed before the second item

  • 0: The items are equal

  • 1: The first item should be placed after the second item


1 Vote 1 ·

Hi @Ehren ,

To be honest I am still trying to understand completly the case in List.Sort wiht Value.Compare, if you can give more light I will appriciate so much. What I undestand so far taking the following:

Example=
List.Sort (
{ 3, 2, 1, 4 },
( a, b ) =>
Value.Compare ( 1/a, 1/b )
)

Then, internaly the function took two items form the list at random (It can be any two items, in any order), let say: 3 and 1

So in we have: Value.Compare ( 1/3, 1/1 ) and the result -1, thus, 3 should be placed before the 1, like: -> { 3, 1 }

But, Now what append? The comparison criteria function take other two items except 3 and 1

And

What append if there an odd number of items in the imput list?

I will appreciate a lot if you can give me a little more light
Best
— Miguel Caballero

0 Votes 0 ·
Show more comments