Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have a situation where I have a list of asset IDs where the only difference between some of them is that some letters are in caps vs lower case (each is unique). When importing the json file into Power Query, the case sensativity is correct, however the sensativity is lost when the data is passed over to Power BI desktop.
Here is some example data:
Power Query Data:
Power BI Desktop Data:
From the above you can see that the intrument ID's all become the same once in PBI Desktop.
I have attempted to prefix the asset IDs with an index generated by Power Query & then strip the index off the asset ID within PBI desktop. When doing this, the ID with the index prefix does retain its sensativity, but losses it once the index has been removed (using the "right" DAX function). I can't use other asset IDs (such as CUSIPs) as not all assets have such values.
How can I import the INSTR_ID field into the Desktop and retain the unique case sensativity of each field?
Solved! Go to Solution.
You could group by the INSTR_ID field, add an index column, merge withe original table by INSTR_ID, then merge the INSTR_ID with the index column. That way the Index suffix will act as the "differentiator"
or, follow the steps outlined in this blog entry:
https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/amp/
Proud to be a Super User!
Paul on Linkedin.
You could group by the INSTR_ID field, add an index column, merge withe original table by INSTR_ID, then merge the INSTR_ID with the index column. That way the Index suffix will act as the "differentiator"
or, follow the steps outlined in this blog entry:
https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/amp/
Proud to be a Super User!
Paul on Linkedin.
Thanks @PaulDBrown for your suggestion. I have already tried this and it worked, but I was then trying to remove the index from the merged value within the Desktop using DAX in order to get back to a clean INSTR_ID value - but when doing this it lost the case sensativity.
If there is no way to get back to a clean (& accurate) INSTR_ID in PBI Desktop, then I'll stick with the merged version.
I added a link to a blog entry in my original reply which enables retaining case sensitivity
Proud to be a Super User!
Paul on Linkedin.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |