Case sensitive/insensitive option
Power BI Desktop's query editor and M language is case-sensitive, so comparisons between strings will return true only when the casing is identical.
Power BI Desktop's data model and DAX language is *not* case-sensitive, so string comparisons return true even with different casing.
There should be options to control this for both places.
Added a vote: problem still not solved and a solution would be really nice.
Dieter Bekaert commented
Added a vote: problem still not solved.
It's even more complicated when your strings contain special characters like the German ß. In PowerBI ß=s, but in Power Query ß<>s, case sensitive or not.
Jon Choe commented
Just ran into this issue while looking up decimal code for the ascii characters. I was using "Character" column as Primary Key. I can no longer use this column as primary key if I add Uppercase and lowercase characters. PowerBI throws message "Column 'Character' ...contains duplicate value 'A' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Workaround shouldn't be necessary and I don't believe the index column workaround will work if your datasets don't contain the exact same record count.
Margaret Magnus commented
I am trying to display all the characters for a given language. I import a table:
When I try to display, all the lower case characters disappear. If I include the (unique) Unicode code points, then all the characters are displayed, but bafflingly, the lower case characters are converted to upper case:
this needs to be resolved
Yes please. Our Sales Ledger accounts are case sensitive. Not good if one customer's details get mixed up with another's.
This needs to be resolved. This is causing me an issue as I have case sensitive strings I need to report on, so Rep is different to rep and PBI changes them both to be rep
Mike Honey commented
FWIW my current workarounds are in Power Query, either using Transform / UPPERCASE from the UI or Text.Upper ( ) in code.
I also struck data recently with non-printing characters at the end: e.g. "ABC" vs "ABC(Tab)". These are counted as different/distinct values in Power Query, but automagically trimmed to a single value in the data model/DAX. My workaround was to Replace Values in Power Query to remove them.
Chris Mlynarczyk commented
This is causing big problems for me as well, Power BI is homogenizing the case of my fields when importing data and I can't seem to find a way to prevent it. I'm working with 15-character Salesforce Account IDs (I don't have admin access to create an 18-character field), which Salesforce creates and interprets case-sensitively. Two records in my data have Account IDs like the following:
Power BI has decided to change both records to "b7M4A000005PLDs". I'm trying to use these Account IDs to generate URLs to navigate back to Salesforce records, and when the case is changed, the links I create navigate to the wrong record. I can understand not being able to interpret distinctions in case, but to actually change the case of my data seems a little much.
For any others working with Salesforce IDs, my workaround is to create a custom column in the query editor, add a "?" to the ID and then tack on a number from an index column, e.g.:
This is enough for Power BI to interpret these differently and not mess with the case, and also allows the generated URLs to continue to function properly. To save you the trouble of trying, you can't shave off this index with a calculated column later, Power BI will go through and homogenize the cases again.
this needs to be resolved
Have we found a solution to this problem yet?, I am facing same issue
For The American Red Cross, it is essential that when values are presented in reports they are presented in the accurate case. Our reports include blood characteristics and a 'c' characteristic in blood is different that a 'C' characteristic. As a use case, if our reports indicate that we need to deliver 'C'K-s' blood to a specific hospital for patient consumption and we deliver 'c-k-s', we will be delivering the incorrect blood, which could have catastrophic effects.
Alexander Schleeh commented
This cost me several hours, because I was trying to figure out why my imported data contained duplicate values. Turned out there were no duplicates - Power BI just wrongly interpreted some values as duplicates. More so even, Power BI actively changed values (e.g. from "MULTIMAX" to "Multimax") without my knowledge, which it shouldn't have, because the values were unique when interpreting them case-sensitively.
Please add the mentioned options to be able to control the behaviour. Thanks!
Paul Bowles commented
Yes!! This absolutely needs to be an option in Power BI Desktop & DAX. There was a previous post from a couple years ago that used an index column in Power Query as a workaround. While this is certainly helpful, any table/matrix visualizations that show the underlying data (e.g. Customer ID from Salesforce.com) simply grab the first (case insensitive) entry in the table. If someone is then trying to take that value and look it up in Salesforce.com for more detail, they can get confused if they are looking for the incorrect case sensitive entry. This is particularly true if the person doing so is not familiar with (or doesn't care for) the nuances of Power BI & DAX.
Amanda Carriveau commented
I agree, this makes sense and would make things much easier!
Ben Sacheri commented
Microsoft, when considering a solution, handling case sensitivity of table data may need to be configured differently than table schema. In my case I had a column heading named "Task" that was renamed to "TASK". This broke a lot of steps. I expected that field names would be treated case insensitive the same way as in SQL.
Anand kumar commented
I am also facing problem, I want to show data as it is available in table but power bi don't provide this option it convert automatically same case which find first