Forum Discussion
Table.Sort, unexplainable behaviour with a two-argument function
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"
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!
3 Replies
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!
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!
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!
