Data Modeling Uppercase and Lowercase issues

[mancs]

Hello,

I'm pulling data from a MYSQL server and among the data that I have there several entries which have similar input however they differ from each other by having some of the letters in their composition UPPERCASE or LOWERCASE, the reasoning behind this is beyond me since I didn't create the database. Regardless, I need to accept all of the entries, however, whenever I apply my modifications to the query editor it transforms part of the entries from LOWERCASE to UPPERCASE values which create duplicates, which in turn doesn't allow me to have a table with unique values. There several examples of this disturbance, the screens bellow show once such case. Any ideea on how to retain the values unchanged from Query Editor to Data View?Query Editor

Data View

[MalJan19]

This is a real issue, and in my case is causing many to many relationships where the true relationship is one to many. Please Power BI Team, make an option to maintain case integrity.

[Jmenas]

Hi all,
I see that was 2019 the issue still for us. @mancs  do you if there is a fix for this? 
@Vicky_Song is there an ETA to fixing the issue? This creates a lot of extra effort while trying to debug it. 

Best,

J.

[fwde]

This is still a big issue! Data is not displayed in PBI as it is stored in the original data. There must be an option to keep data integrity! 

[klittle2019]

Surprised this is still an issue as I need to see differences in text in my reporting for data validation. Microsoft please address this issue!

[Anonymous]

Seems to be a long lasting issue. I didn't came accross this issue until today were I discovered a many-to-many relationship in a new datamodel I'm creating.

After using Text.Upper([Column Name]) in Query M it seems not to be possible to pull out distinct values from the source table (I tried "remove duplicates" but nohting seems to happen). That might solve the issue in my case.

Dennis

[DebraNorth]

Data Modeling Uppercase and Lowercase issues - I'm surprised and disappointed that Power BI hasn't addresssed this issue.  It's causes issues with our data also.  Odd thing is, the table has the data with the correct upper/lower case letter but once you transform the data, there's at least one value that doesn't hold the upper/lower. 

Please find a fix for us.

[Anonymous]

My understanding is the Data does not change in the dataflows or models but what happens when the data is being pulled into visualisation  compression is applied to speed up the views and this compression basically takes the first time the char  is seen and keeps that case for all other in the same view element.

In our data the char case was important so the only workable solution I found was to transpose those char to something else not used - I was lucky in my case we were using abc & ABC so i changed abc to xyz.

But it would have be helpful if you could select if this compression is used by view element but no idea what impact this would have on performance.

Andrew  

[PAPutzback2]

This is the craziest bug I have seen to date. I am creatign a report to be used by Data Governance to clean up data. One of the reports looks for states that do not match the ISO standards. So Ok ( Oklahoma ) Is not the same as OK. So if I flag the record with Code Invalid and I the value they see is OK, then they think my report is wrong.  

[pawel_osipowski]

@Member123456 please react to this and help address it as a bug. This is serious issue causing data disintegrity in many PBI models as many stated here

[Harsh1708]

Hello,

I was also having the same issue but got the solution after some effort.

You can try converting your string into ASCII code into the query editor using M query.

Create a custom column in the query editor and convert your string letter by letter into relevant ASCII Code.

For example, a Column having the string = Product ID 

 Text.From(Character.ToNumber(Text.Middle([#"Product ID"],0,1))) &"|"&

Text.From(Character.ToNumber(Text.Middle([#"Product ID"],1,1))) &"|"&
Text.From(Character.ToNumber(Text.Middle([#"Product ID"],2,1)))&"|"&
Text.From(Character.ToNumber(Text.Middle([#"Product ID"],3,1)))

You will get ASCII code for your String separated by "|" letter by letter.

Now after loading in the BI table, Split the ASCII code by "|", and using UNICHAR  decode the ASCII Code.

The method is a little typical but you will get the result.