Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
G_Whit-UK
Helper II
Helper II

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

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:

G_WhitUK_1-1667907533615.png

 

Power BI Desktop Data:

G_WhitUK_2-1667907541933.png

 

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?

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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/ 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

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/ 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.