Retain case sensitivity when passing values from Power Query to Power BI

[G_Whit-UK]

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?

[PaulDBrown · Accepted 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/ 

[G_Whit-UK]

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.

[PaulDBrown]

I added a link to a blog entry in my original reply which enables retaining case sensitivity