Text auto transforming from ALL CAPS to lower case

jwilmoth74 · ‎07-18-2017

Pulling data from SQL and in the Query editor, the text displays correctly as ALL CAPS

After applying and refreshing, the table in the fields pane contains a value that is Lower Case.

It appears the Power BI is just forcing the text to lower case. This is not consistant on all the rows and appears just this one row is doing it.

v-haibl-msft · ‎07-20-2017

@jwilmoth74

Do you mean that the data is displayed as ALL CAPS but once we close and apply Query Editor, the data is displayed as Lower Case? I just tested on my side but cannot repro the same issue. Could you please provide a screenshot? And a PBIX file which can be used to repro is preferred.

Best Regards,
Herbert

Vicky_Song · ‎07-20-2017

ibarrau · ‎09-10-2021

Hi. I can't believe this is still there. This bug has been there for way too much time. Let me explain it better.

The issue is that Power Bi will asume that the first format of a string will be the case for all of them. This meas that if you have in the same column "CAPEX" and later "capex". It will use "CAPEX" for the whole column after you close and apply. It's a pain if you are talking about Keys or IDs with case sensitive. They will be very difficult to find and remove duplicates from power query won't find it but you won't be able to relate it because after the close and apply it changes.

If you need to reproduce it just entry data with different cases of the same text. I can show a very simple example here:

Please do something because the change of data is critical for me.

dinkumdata · ‎10-31-2021

I am having the same issue...I am using a column of type Text in a calculated column which I am then using as a join key column and because of this automatic conversion of Uppercase to Lowercase it is resulting in duplicates values on my join key column when it should in fact be unique which is causing issues in the resulting joined table. Please can someone shed some more light on how to fix this or when this bug will be resolved?

ibarrau · ‎11-01-2021

Super user collegues have helped me to understand more about this. I'll write the answers to help new users that found this issue for the first time.

Edhans: "Tabular is not case sensitive, and will not treat those separately. That is not a Power BI thing, but a SSAS Tabular thing, that would filter to Power BI, AAS, etc. I think that is a pretty big change in the engine to support without breaking existing models."

D_gosbell: "So Tabular does have some support for case-sensitivity, but it's currently a server level setting. So it could be set for on-prem SSAS and possibly ASAzure, but for PowerBI the cloud service is set to case-insensitive by default and I think that is the correct choice."

I tend to agree with Greg Lowe's blog on this topic: https://blog.greglow.com/2018/01/23/opinion-case-sensitivity-pox-computing/