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

[Jonathanvdb]

Hi, was this issue ever addressed? It does not seem so...? 

I 'm having the same issue with reagrds to i.e. two invoices where PBI is seeing them as one. 

The uderlying system my client usess allows users to capture invoices by example Q07033 and q07033 - these are two seperate invoices that are being combined as one in the frontend reports. They are in fact not related to each other. 

Did anyone come right with a workaround?

[PAPutzback2]

What is the source of your data? If you have the ability to clean your data before ingesting it into Power BI, you can use a function to determine the case of the first letter and create a flag in the data. iif Case(left(InvoiceID,1) = Upper()) than 'Source System 1', 'Source System 2') as 'Source System'.

Or you can go through the hoops here and hack the data in your m query
Chris Webb's BI Blog: Power BI And Case Sensitivity Chris Webb's BI Blog (crossjoin.co.uk)

[Jonathanvdb]

Thanks for the feedback and your suggestion PAPutzback2. I was thinking something along thise lines.

I will need to see how many variances exist in the data to confirm your proposed solution. 

Thanks for the Chris Webb article, will go through it and determine the best route forward. 

Thanks again.

[Alicia_Anderson]

I second what DebraNorth said above: 

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.

[liz72]

Not sure if it helps but there looks to be a bug where data will load (but not show in the query as) UPPERCASE. This seems to be the case after I change some other 'number-type' columns to text. The data is from an SQL import.

[Badbilly]

I'm glad I found this thread - I thought I was losing it - my query was telling 2 text strings were different when they were identical in the visualisation due to the case having been changed in one of them.  This definitely needs to be a 'feature' that users can switch off.  When trying to validate data integrity and consistency, you need to be able to check taking case into account.   

[Rudi_Li]

Source data should never be changed "automatically" by an application. This makes checks impossible and never 100 full proof.

Please fix this bug.

[andrereh]

It would be great if Microsoft products don't always assume that they know the users' data better than the users themselves. Please fix this!

[Len_Barr]

If anyone is having an issue with string casing changing the relationships between your tables (which use the string as the column on which the relationship is built), then I'd highly recommend you avoid using this column to build your relationships on. 

Instead, create a numerical key column on both sides of the relationship and use this - numerical keys don't get affected by UPPER, lower or ProperCase conversions, because they are numbers. That'd be silly.