Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

0

Data Modeling Uppercase and Lowercase issues

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 EditorQuery Editor

 

Data ViewData View

 

Status: Delivered
Comments
Jonathanvdb
Regular Visitor

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
Helper II

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
Regular Visitor

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
Resolver I

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
Advocate I

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
New Member

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
New Member

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

Please fix this bug.

andrereh
Regular Visitor

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!