mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,245
- Office Version
- 365
- Platform
- Windows
Dear Team,
In Power Query M Code I am trying to figure out how the internal algorithm works for the comparer functions:
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:
If we use this formula:
=Table.Group(AddDataTypes, {"Supplier"}, {{"TotalCosts", each List.Sum([Costs]), type nullable number}})
We get:
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:
I have broken apart how it looks like the algorithm works here so that we can see the resultant array:
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!
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
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!
