cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01
Super User
Super User

Seeking Explanation of Comparer.OrdinalIgnoreCase

I am trying to understand how comparer-ordinalignorecase works. I have run the following test.

 

= #table({"col1"},{
{Comparer.OrdinalIgnoreCase("Abc", "abc")}/*row#=1*/,{Comparer.OrdinalIgnoreCase("Abc", "cab")}/*row#==2*/,{Comparer.OrdinalIgnoreCase("Abc", "xya")}/*row#==3*/,
{Comparer.OrdinalIgnoreCase("Abc", "efg")}/*row#==4*/,{Comparer.OrdinalIgnoreCase("Abc", "")}/*row#==5*/,{Comparer.OrdinalIgnoreCase("Abc", "0")}/*row#==6*/,
{Comparer.OrdinalIgnoreCase("Abc", "ABC")}/*row#==7*/,{Comparer.OrdinalIgnoreCase("Abc", "unknown")}/*row#==8*/,{Comparer.OrdinalIgnoreCase("Abc", "#")}/*row#==9*/
})

 

 

smpa01_2-1663259652938.png

I understand that they return 0 on the exact match (row#1 and 7) but how do I know when they return -1 and +1? I can't seem to understand what it returns in what circumstances.

 

The official doc says the following but what exactly are Ordinal rules to compare the provided values x and y.

 

smpa01_3-1663259778145.png

 

I want to utilize this in production, once I fully understand the behaviour.

 

I expected this to return 0 for row 1 and row 7 and a different value (either 1 or -1) for other rows. But why does it return 3 values? Are there more values that can be returned by the function that I currently don't know of?

@ImkeF @AlexisOlson 

 

 

Thank you in advance.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

The word "ordinal" usually means there is some sort of order involved. For any two objects A and B, there are three possible options when you compare them: A > B, A = B, A < B which correspond to 1, 0, and -1 for this comparer.

 

You can achieve the same thing as follows:

let
  Source = #table(
    type table [col1 = text, col2 = text], 
    {
      {"Abc", "abc"},     /*row#==1*/
      {"Abc", "cab"},     /*row#==2*/
      {"Abc", "xya"},     /*row#==3*/
      {"Abc", "efg"},     /*row#==4*/
      {"Abc", ""},        /*row#==5*/
      {"Abc", "0"},       /*row#==6*/
      {"Abc", "ABC"},     /*row#==7*/
      {"Abc", "unknown"}, /*row#==8*/
      {"Abc", "#"}        /*row#==9*/           
    }
  ),
    #"Added Custom" = Table.AddColumn(Source, "Compare", each
      if      Text.Lower([col1]) > Text.Lower([col2]) then  1
      else if Text.Lower([col1]) < Text.Lower([col2]) then -1
      else 0
    ,Int64.Type)
in
  #"Added Custom"

AlexisOlson_0-1663276094204.png

The ordering is alphanumeric ignoring case.

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

The word "ordinal" usually means there is some sort of order involved. For any two objects A and B, there are three possible options when you compare them: A > B, A = B, A < B which correspond to 1, 0, and -1 for this comparer.

 

You can achieve the same thing as follows:

let
  Source = #table(
    type table [col1 = text, col2 = text], 
    {
      {"Abc", "abc"},     /*row#==1*/
      {"Abc", "cab"},     /*row#==2*/
      {"Abc", "xya"},     /*row#==3*/
      {"Abc", "efg"},     /*row#==4*/
      {"Abc", ""},        /*row#==5*/
      {"Abc", "0"},       /*row#==6*/
      {"Abc", "ABC"},     /*row#==7*/
      {"Abc", "unknown"}, /*row#==8*/
      {"Abc", "#"}        /*row#==9*/           
    }
  ),
    #"Added Custom" = Table.AddColumn(Source, "Compare", each
      if      Text.Lower([col1]) > Text.Lower([col2]) then  1
      else if Text.Lower([col1]) < Text.Lower([col2]) then -1
      else 0
    ,Int64.Type)
in
  #"Added Custom"

AlexisOlson_0-1663276094204.png

The ordering is alphanumeric ignoring case.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors
Top Kudoed Authors