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!)
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. 🙂
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?
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!)
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).
@ken – VBA is case sensitive
@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.
Is it possible to use this when joining two columns
For my understanding, these 2 techniques work independently from each other.
Pls provide more details of what you want to achieve.
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.
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).
Hi,
I tried to use Table.NestedJoin and play with 5th argument but it didn’t work and I solve this issue with change this function to Table.FuzzyNestedJoin without any extra prameter.
List.Distinct({“One”, “ONE”, “one”}, Comparer.OrdinalIgnoreCase)
will only return the first item !
Thanks for the careful reading & feedback Frank, very much appreciated! (Have edited the text!)
Pingback: Table.Group: Exploring the 5th element in Power BI and Power Query – The BIccountant
Pingback: Power BI case insensitive with JSON – program faq
Hi,
After using Comparer.OrdinalIgnoreCase, I found an error in the last row. Can you please help me on this(The whole row has got the same error).
= Table.SelectRows(#”Renamed Columns5″, each Text.Contains([Risk_Reason__c], “quote”, Comparer.OrdinalIgnoreCase))
Below is the error that I encountered.
ERROR at Row:1:Column:34
unexpected token: ‘TRUE’
Thanks
Hi Suman,
this kind of error means that you have an undetected error in a previous step that just shows up now because you use a Table.SelectRows-command.
If you select all columns -> filter out rows with errors before this step, it should return the results OK.
/Imke
Hi
This works if you are trying to compare a sub string. Is there a way I can compare the complete cell value
Pingback: Chris Webb's BI Blog: Power BI And Case Sensitivity
Ran into the problem today of just comparing two text strings case-insensitive. Converting them to all upper/lower case seemed clunky, but discovered one can do simply: Comparer.OrdinalIgnoreCase(“ABC”,”abc”) which will return
0if they are the same without regard to case.