Culture.Current
This is a variable that returns current culture. Current culture is described by symbols like "cs-cz" (for Czech language) or "en-gb" (for UK english). Culture is defined in Options, under Regional Settings, both in PowerPivot and Power BI Desktop.
The code below would return string "sr-Latn-RS". This blog post has, bellow, attachment "Language codes.xlsx" which contains list of all language codes.
| let Source = Culture.Current in Source |  |
Comparer.FromCulture()
Imagine that we want to compare two values based on specific culture and case sensitivity. To compare strings "A" and "a" based on "sr-Latn-RS" culture while ignoring case sensitivity, we could use some function that look like this:
SomeFunctionUsedForComparison( "sr-Latn-RS", true, "A", "a" )Power query can do the same thing but it does it in two steps. First step is to create comparison function:
ComparisonFunction = Comparer.FromCulture("sr-Latn-RS", true)Second argument is for case sensitivity. Default is false (sensitive). This argument is optional.
After this, we can use our new ComparisonFunction to make comparison when ever we need it.
| Second step is to call this function when we need it. We just supply two values to compare. ResultOfComparison = ComparisonFunction( "A", "a") We can see on the right, that this new function receives two arguments to compare. Those two arguments could be of any type. As a result, this new function returns a number. That number is "0" for equality and "-1" or "1" for inequality. |  |
Here are three examples of this function. For brevity we will merge two steps into one step:
| Comparer.FromCulture("sr-Latn-RS", true)( "A", "a" ) | Returns "0". Equality. |
| Comparer.FromCulture("sr-Latn-RS", false)( "a", "A" ) | Returns "-1". Inequality. "a" < "A". |
| Comparer.FromCulture("sr-Latn-RS", false)( "A", "a" ) | Returns "1". Inequality. "A" > "a". |
We can see that, because of different case sensitivity, we get different results.
Comparer.Ordinal()
This function is used to compare two values. Comparing is based on ordinal rules. Lets asume that we compare:
| æ | =UNICHAR(230) 'by excel formula | AND | ae | =UNICHAR(97)&UNICHAR(101) 'by excel formula |
If we compare this two values with Comparer.FromCulture, for the result we would get equality.
| Comparer.FromCulture( "sr-Latn-RS", true )( "æ", "ae" ) | Returns "0". Equality. |
This is because, culture help us to compare values that have similar meaning.
Comparer.Ordinal() is ortodox function. It compares unicode codes, and not the meaning of symbols. This function will compare code "230" with codes "97" and "101". It will find that this two values are not the same. Let's see three examples for this function:
| Comparer.Ordinal("æ","ae") | Returns "1". This is because 230 > 97. |
| Comparer.Ordinal("ae","æ") | Returns "-1". This is because 97 < 230. |
| Comparer.Ordinal("æ","æ") | Returns "0". This is because 230 = 230. |
As we see, when comparing numbers, we know which number is bigger. That is way we get results "-1,1" depending on which number is larger or "0" if numbers are equal.
"ae" is consider as two unicode numbers, so for concluding sort order we only need code number for letter "a".
Comparer. OrdinalIgnoreCase()
This function is the same as Comparer.Ordinal(), except it is case insensitive.
| Comparer.OrdinalIgnoreCase( "A", "a" ) | Returns 0. |
| Comparer.OrdinalIgnoreCase( "a", "A" ) | Returns 0. |
| Comparer.OrdinalIgnoreCase( "A", "A" ) | Returns 0. |
| Comparer.OrdinalIgnoreCase( "A", "b" ) | Returns -1. |
| Comparer.OrdinalIgnoreCase( "b", "A" ) | Returns 1. |
Comparer.Equals()
This function can use previous functions as arguments to compare two values. Here are three examples:
| Comparer.Equals( Comparer.FromCulture( "nl-NL", true ), "a", "A") | Returns "true". |
| Comparer.Equals( Comparer.Ordinal, "a", "A") | Returns "false". |
| Comparer.Equals( Comparer.OrdinalIgnoreCase, "z", "z") | Returns "true". |
As we can see, we are using three arguments. First one is function defining how to compare values in other two arguments. Comparer.Equals() returns "true" if values are equal and "false" if they are different.
List of all culture codes and examples for comparer functions can be found in this file: