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.
Mike Honey
shared this idea
33 comments
-
Anonymous
commented
Agreed; this flaw makes working with Salesforce.com difficult, since (for some odd reason) Salesforce has duplicates of a 15 string alphanumeric ID (for accounts, opportunities etc) and is case sensitive.
-
Mimi
commented
Any idea when the bug will be fixed?
-
Andrew Bridson
commented
I have a text column that I ingest and it renders fine. Then I create a new column (within M running as a Dataflow) by looping through the text and selecting certain positions in the string using Text.AT() and this all works fine but when I look at it a report it changes.
Anyone any ideas how to force the correct display same as the original ingested column.
It seems nuts that PowerBI reports do not display what is actually held in the field.
-
Kushal Farkase
commented
We also faced this issue. In our case, we have 3 tables with one common column. We are merging this tables into one using common column. We have two entries with different case. We want them to treat as different. But during merging, Power-BI treating it as one and many to one relationship is violating. We raised a ticket on this one but they said it is product limitation and even there are no alternative. They asked to vote the post on forum, so that they will consider it in update.
-
Anonymous
commented
Power BI Page level filters are giving both filter values "ABC" and "abc".They should be treated same.When will this be fixed.
-
David Leonard
commented
This is a big problem for me as I use case-sensitive values in reports such as having values of "minValue", "MinValue", "minvalue" and "MINVALUE" and PBI is changing the data automatically. Having an option to auto-change text values would be appropriate given the number of users such as myself facing the same issues.
-
Michael Mowle
commented
I agree with the below comments. My problems are specific to the Column Headers for my tables which are created using data processed through Power Query. Just ran into the situation today where a Column Name used to be User_ID is now USER_ID and, considering this is a key from linking multiple tables, effectively has my reports "down"
-
Josh
commented
This would be awesome and a huge help with Salesforce data.
-
Anonymous
commented
Added a vote: problem still not solved and a solution would be really nice.
-
Dieter Bekaert
commented
Added a vote: problem still not solved.
-
Anonymous
commented
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.
-
Anonymous
commented
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:
English A
English a
English B
English b
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:
English >
A 0x41
A 0x61 -
Anonymous
commented
this needs to be resolved
-
Graham
commented
Yes please. Our Sales Ledger accounts are case sensitive. Not good if one customer's details get mixed up with another's.
-
Trev
commented
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:
b7M4A000005PLDs
b7M4A000005PLDSPower 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.:
b7M4A000005PLDs?1
b7M4A000005PLDS?2This 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.
-
elfersi
commented
this needs to be resolved