How to tame Case Sensitivity in Power Query and PowerBI

Have you heard of “Comparer.OrdinalIgnoreCase”?

If the answer is “No” (or something similar), you will be very delighted to hear that this is your key to disable case sensitivity in comparing operations.

So if you want to check if a substring like “car” is contained somewhere, you can write:

Text.Contains(MyText, "car", Comparer.OrdinalIgnoreCase)

and the expression will return true for all cases of car like: CAR, Car, caR… .

The official documentation looks like this:

 Text.Contains(string as nullable text, substring as text, optional comparer as nullable function) as nullable logical

So the “optional comparer as nullable function” makes all the difference here. Documentation is very sparse, but you might find this option in other M-functions that use equation criteria as well.

Thanks to Colin Banfield for bringing this to my attention.

Edit 2nd Nov 2016:

List.Distinct({"One", "ONE", "one"}, Comparer.OrdinalIgnoreCase)

will disable case sensitivity here and return the first item in the list

Table.Distinct(
Table.FromRecords({[A="one", B="two", C=2], [A="ONe", B="TWo", C=3], [A="ONe", B="Two", C=3]}), 
{{"A", Comparer.Ordinal}, {"B", Comparer.OrdinalIgnoreCase}} )

will remove duplicates with different case-sensitivity parameters per column (thx to KHorseman for this tip!)

Comments (14) Write a comment

  1. Interesting. Personally I’d just convert to lower case then look for car. I suppose this takes on less step, but it’s kind on an ugly function to have to spell. 🙂

    Reply

    • You bet! Very much hoping for an IntelliSense-Xmas-Pressie 🙂

      I think the real interesting use cases are where there is actually no alternative way to it, like the remove-duplicates. I’ve seen so many cases where people haven’t even been aware that they end up with duplicates in the (PP)datamodel if they have different cases in their strings.

      BTW: Do you (by any chance) know why Microsoft has decided to default M/Power Query to case sensitive?

      Reply

  2. Case sensitive on searches like this, or case sensitive for the entire language? I’m honestly not sure why either was chosen, but I’m far more frustrated with the entire language being designed this way. It’s a complete 180 from Excel functions, Power Pivot functions and VBA. (Although RibbonX is case sensitive!)

    Reply

    • Thx Ken 🙂

      I meant the first: Just wondering which benefits of the case sensitivity made them “compensate” for the irritations this “I removed all my duplicates but still cannot connect my tables in the data model?” causes to many users. This is not a good user experience in a “self-service” BI-Tool in my eyes.

      Here a default that matches the data model (output) and Excel (still as one of the primary inputs) would have made much more sense (with an option for case-sensitivity where that actually is required).

      Reply

    • @sam, VBA’s text comparison functions are, but I’d argue that VBA as a language is not. When you type in all lowercase, it converts it to the case it uses. Because you can type in lower (or upper) and it auto converts, I don’t classify that as case sensitive. Excel formulas exhibit the same methodology as VBA in that regard, where Power Query and ribbonX stay exactly as you typed and fail if the casing is incorrect.

      Reply

    • For my understanding, these 2 techniques work independently from each other.
      Pls provide more details of what you want to achieve.

      Reply

      • Assume there is a column of Department names in the Fact table and Department in the Dim Table and we created a left outer join from Fact to Dim when we pull the related columns in the the Fact table it should ignore case and pull the columns can this be done.

        Reply

        • Hi sam,
          there is a 5th optional parameter in the Table.NestedJoin function, but I cannot get it to work at the moment. Trying to find out and will come back.
          (Otherwise one can use Table.SelectRows, but I’d expect this to be rather slow).

          Reply

  3. List.Distinct({“One”, “ONE”, “one”}, Comparer.OrdinalIgnoreCase)

    will only return the first item !

    Reply

    • Thanks for the careful reading & feedback Frank, very much appreciated! (Have edited the text!)

      Reply

  4. Pingback: Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant

  5. Pingback: Power BI case insensitive with JSON – program faq

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz