Forum Discussion
Different capitalization in Power Query and loaded data affecting calculated columns
I solved the issue by creating a new index column in Power Query for my data. After that, I copied the query of that table into a new one (IndexTable) where I only had the IDs containing different capitalised letters and the newly added index (removed all the other columns).
From that point on I can reference IndexTable and the newly added Index Column in Power BI to avoid the initial issue or merge queries in Power Query using the original ID since Power Query is case sensitive.
Helpful links:
https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/
I am an experince solver.
If I have misunderstood, then please try describe the problem better. Thank you.
It think itis relevant because it is best pratice to data cleansing.
If you use UPPER it will convert Abcd, abcD and ABcD to ABCD in you dim and fact tables.
You can then remove duplicates and build your relationships without the worry case sensitive discrepancies.
My problem is exactly the opposite. I want Abcd, abcD aBcd and so on to be as such because my primary key contains IDs which are case sensitive (meaning if I use UPPER, the relationship between them will no longer reflect reality because AbcD and abcD for instance are distinct IDs) The issue arises when data is loaded from Power Query into Power BI because all these IDs which are currently in Power Query:
become this:
And as you can see, that is not what I want because now all IDs have been altered and data loader references the same data to all of them. (Say abcD is Texas and AbCd is Kentucky, and now all my states are Missouri because Abcd is Missouri)
You have given me tips on doing the opposite since I want Power BI to keep all those variations of 'ABCD'.
Hope the problem is clearer now.
Thank you Anonymous for explaining the problem.
Power Bi is case insensivity, so if you have duplicated keys with a different case then they will all defaut to the first value
eg aBcd, abCD and abCd will default to aBcd,
whereas abCD, aBcd, and abCd will default to abCD
I dont think you can override this Power BI feature.
You could use SQL or SSAS but it is best practice to rename source data that has case insensivity before using Power BI
Watch this video
https://www.youtube.com/watch?v=xnKWYKBuW0E
Sorry this is not the answer you want, but it is the correct answer and explains the renaming best practice and the SQL or SSAS work arround.
Please click Accept Solution and thumbs up, because I have gone to the trouble of giving you a quick answer. I am unpaid volunteer and just ask for the kudos as a reward for helping.
Many thanks.