How can we improve Power BI?

← Power BI Ideas

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.

175 votes
Sign in
(thinking…)
Sign in with: facebook google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Mike Honey shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

25 comments

Sign in
(thinking…)
Sign in with: facebook google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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

  • Graham commented  ·   ·  Flag as inappropriate

    Yes please. Our Sales Ledger accounts are case sensitive. Not good if one customer's details get mixed up with another's.

  • Trev commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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
    b7M4A000005PLDS

    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.:

    b7M4A000005PLDs?1
    b7M4A000005PLDS?2

    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.

  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

  • Ben Sacheri commented  ·   ·  Flag as inappropriate

    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.

    https://stackoverflow.com/questions/50842494/is-it-possible-to-ignore-case-sensitivity-of-column-headings-in-power-query

  • Anand kumar commented  ·   ·  Flag as inappropriate

    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

← Previous 1

Power BI Ideas: Desktop

Feedback and Knowledge Base

(thinking…)

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.