Table.Sort, unexplainable behaviour with a two-argument function

[WesleyS]

I've created a simple table where I want to sort based on a calculation of month (as number) plus the number in the Value-column.

When I sort using a one-argument function (the step #"Only a"), using:

    (a) => a[Sort function]

I get the expected behaviour, sorting in scending fashion.

When I sort using a two-argument function, using the same function and not referencing the second argument:

    (a, b) => a[Sort function]

I get a sort order that switches between a larger and a smaller value.

I don't have the brains to figure out the pattern, or why Power Query is doing this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMlSK1YlWcktNArKNwGzfxCIg2xjMhqgxQVJjiqTGDEmNOZIaCyQ1lkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.StopFolding(
Table.AddColumn(#"Changed Type", "Sort function", each Date.Month(Date.From("01-" & [Month] & "2025")) * 10 + [Value], Int64.Type) //month as number * 10 + [Value]
),
    #"Only a" = Table.Sort(#"Added Custom",
    (a) => a[Sort function] //Sorts ascending
),
    #"a and b, same function" = Table.Sort(#"Added Custom",
    (a, b) => a[Sort function] //switches between bigger and smaller???
),
    #"a minus b" = Table.Sort(#"Added Custom",
    (a, b) => a[Sort function] - b[Sort function] //also sorts ascending
)
in
    #"a minus b"

[DNMAF · Accepted solution]

Hi @WesleyS ,

Power Query has certain expectations concerning the comparer function. If you don't meet these requirements Power Query doesn't necessarily react with an error message but with a unpredictable behaviour. Randomly switching is not unusual.

The requirements for the comparer function are:

• Two parameter (a,b). You miss this requirement in your first attempt
• The sort function should compare two rows (a and b). You don't do that in attempt 1 and 2
• The sort function must return certain values: (attempt 3 does that by accident)
  • a negative number => a comes before b
  • a zero => rows are equal
  • a positive number => a comes after b
    
    

In your case it shouldn't even be necessary to use a comparer function, the following should work:

Table.Sort(#"Added Custom", {{"Sort function", Order.Ascending}})

If you absolutely need to use a comparer function, the solution should be like this one:
Table.Sort(#"Added Custom", (a, b) => Value.Compare(a[Sort function], b[Sort function]))

Hope that helps!

[WesleyS]

Yeah the actual function wasn't important, I just needed something to test with.

Glad to hear that PQ's odd behaviour with the (a, b) version is not unusual, since I haven't told PQ how to relate the rows.

Thank you!

[WesleyS]

Yeah the actual sort function used isn't important, I simply needed something to test out the (a, b) => a[Sort function] version. 

Well, I'm glad to not it's not me going crazy but simply a quirk of me not telling Power Query how to act.

Thanks!