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/
Super UserI 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.