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
35 comments
-
T2
commented
Very disappointing gap in rigor.
-
vic
commented
just in case. you can use the 'Comparer.OrdinalIgnoreCase' parameter in Power Query in such cases to filter out case sensitive duplicates, for instance 'Table.Distinct(Table, {"ColumnName", Comparer.OrdinalIgnoreCase})'. for further info see https://community.powerbi.com/t5/Desktop/quot-Remove-Duplicate-quot-doesn-t-remove-all-duplicate/td-p/84593
-
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.