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

Reply
Anonymous
Not applicable

Changing the ID letter case after loading into model

Hi,
I encountered a strange case, probably bug, with an ID column. The ID's in my database are unique for each record, but only including case-sensitivity (which I know that Power Query applies).
So, in Power Query I have for example 3 records with ID's, which are almost identical, but still different including case sensitivity, as below (the last 3 signs are different):
a031o00001EpIYH
a031o00001EpiYh
a031o00001Epiyh

As far as I'm in Power Query, everything is OK, there are no duplicates in the database. But after loading into model, something really strange happens - all three ID's convert into one identical - a031o00001EpIYH and I can see it on Data label after filtering.
First I thought, that except Power Query, DAX and relations could be not case-sensitive (which is problematic enough and I think it really should be consistent), but why it changes and unifies my IDs ?

For that reason, I cannot create any relation, because Power BI claims, that there are duplicates in this column. When I open Power Query again, the ID's differs correctly, so I can't de-duplicate anything.
The ID column type is "Text". The main question is - why Power BI during loading into model can change capitals into lowercases and contrariwise in the Text columns? Does it also can make such changes in other columns, like with "Last name" ?
And is there any clarification for such strange case or is it a bug or something?
I will be grateful for any help 🙂

2 REPLIES 2
v-lionel-msft
Community Support
Community Support

Hi @Anonymous ,

I created a test table as follows

aa8.PNG

Although the software is case sensitive in the power query, but in data view, the software isn't case sensitive when the value is exactly the same except for the case. In other words, DAX is not case sensitive.

aa9.PNG

 

For your Problem, it has been mentioned many times in the forum, maybe you can refer other answers:

Creating Case Sensitive Table Relationships  

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MartynRamsden
Solution Sage
Solution Sage

Hi @Anonymous 

 

Chris Webb wrote a great blog post about Power BI case sensitivity in October 2019.

You can find it here: blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ 

 

There's a section near the end where he explains how to add a zero-width spaces in Power Query to force case sensitivity in the Power BI engine.

Hopefully this will help in your situation.

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors