List.Min, List.Max, Table.Group: Algorithm for the comparer functions?

[mgirvin]

Dear Team,

In Power Query M Code I am trying to figure out how the internal algorithm works for the comparer functions:

• Comparer.Equals
• Comparer.FromCulture
• Comparer.Ordinal
• Comparer.OrdinalIgnoreCase
• Custom Function for comparing like: if x > y then 1 else if x < y then -1 else 0

Help says that these functions generate an array of ones and zeros and negative ones to help determine ordering, sorting, grouping, max and min calculations and other calculations in functions like List.Sort, Table.Group, List.Min, Lit.Max and many more.

But help is very sparse on exactly what x and y represent and how the resultant array of ones, zeroes and negative ones actually determine the final result.

As for as I can tell, for a single column (comparing sequential rows in a column) comparison:

x = current row in a column

y = next row in column

and

x > y then 1 or x < y then -1 or x = y then 0.



My first question about the algorithm is, if we have these formulas:

MinAlgorithmn = List.Min({-1,-5,0,6,6,3},null,(x,y) => if x > y then 1 else if x < y then -1 else 0) = -5

DefaultMin = List.Min({-1,-5,0,6,6,3}) = -5

MaxAlgorithmn = List.Max({-1,-5,0,6,6,3},null,(x,y) => if x > y then 1 else if x < y then -1 else 0) = 6

DefaultMax = List.Max({-1,-5,0,6,6,3})

Then the original data set would be:

{-1,-5,0,6,6,3}

The algorithm would be:

-1 > -5 algorithm yields:= 1

-5 < 0 algorithm yields:= -1

0 < 6 algorithm yields:= -1

6 = 6 algorithm yields:= 0

6 > 3 algorithm yields:= 1

The resultant array would be: {1,-1,-1,0,1,??}

How in the world does this array {1,-1,-1,0,1,??} pick out from the array, {-1,-5,0,6,6,3}, the -5 for the minimum value and 6 for the maximum value? What pattern am I missing? Or is this not at all how the List.Min and List.Max functions work?



My second question about the algorithm is, if we have this starting table:

Supplier	Costs
Cœur Work Landscape	2100​
Coeur Work Landscape	1522.36​
Aerial Surveyor	3582.44​
Ærial Surveyor	550.75​
Æsthetic Landscape	1992.25​
Cœur Work Landscape	481.26​
Ærial Surveyor	1005.75​
Coeur Work Landscape	250.99​

If we use this formula:

=Table.Group(AddDataTypes, {"Supplier"}, {{"TotalCosts", each List.Sum([Costs]), type nullable number}})

We get:

Supplier	TotalCosts
Cœur Work Landscape	2581.26
Coeur Work Landscape	1773.35
Aerial Surveyor	3582.44
Ærial Surveyor	1556.5
Æsthetic Landscape	1992.25

If we use this formula:

= Table.Group(AddDataTypes, {"Supplier"}, {{"TotalCosts", each List.Sum([Costs]), type nullable number}},null,Comparer.FromCulture("en-US",true))

We get:

Supplier	TotalCosts
Cœur Work Landscape	4354.61
Aerial Surveyor	5138.94
Æsthetic Landscape	1992.25

I have broken apart how it looks like the algorithm works here so that we can see the resultant array:

Supplier	Costs	x	y	Comparer.FromCulture
Cœur Work Landscape	2100​	Cœur Work Landscape	Coeur Work Landscape	0​
Coeur Work Landscape	1522.36​	Coeur Work Landscape	Aerial Surveyor	1​
Aerial Surveyor	3582.44​	Aerial Surveyor	Ærial Surveyor	0​
Ærial Surveyor	550.75​	Ærial Surveyor	Æsthetic Landscape	-1​
Æsthetic Landscape	1992.25​	Æsthetic Landscape	Cœur Work Landscape	-1​
Cœur Work Landscape	481.26​	Cœur Work Landscape	Ærial Surveyor	1​
Ærial Surveyor	1005.75​	Ærial Surveyor	Coeur Work Landscape	-1​
Coeur Work Landscape	250.99​	Coeur Work Landscape

How in the world does this array {0,1,0,-1,-1,1,-1,??} make the final grouping group based in “en-US” equivalent letters? What pattern am I missing? Or is it working some other way?

Thanks, Team!