Different capitalization in Power Query and loaded data affecting calculated columns

[Meep]

Hi there, 

I am having an issue which is affecting the accuracy of my work. I noticed that Power BI table view does not load all the variations of an entry, but it takes the first one and uses it for subsequent similar entries. This in turn affects relationships and/or Lookupvalue measures. 

Power Query 'Table': 

Power BI 'Table':

Why do all IDs get a match? That should not be the case since only 'abCd' has a match in 'Table (2)'

Power BI 'Table (2)'

If i change the last entry in Table (2) from 'ABCDE' to 'ABCD', the Lookup calculated column will no longer work, giving me the error 'A table of multiple values was supplied where a single value was expected'. This clearly shows that Power BI table view does not take into account capitalisation of cells, whereas Power Query does. 

How can I make Power BI Table view do that too?

Thank you, 

[Meep · Accepted solution]

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://community.fabric.microsoft.com/t5/Desktop/use-case-sensitive-to-create-a-correct-relationssh...

https://community.fabric.microsoft.com/t5/Desktop/Creating-Case-Sensitive-Table-Relationships/td-p/1...

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

[Ahmedx]

refer to the following

https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

https://xxlbi.com/blog/power-bi-antipatterns-12/

[speedramps]

Power Query previews only display the firsty 1000 rows, but you can click "load more".

Power BI auto intellegence will make assumptions and auto create relations based on just the first few rows.

It is best pratice to data clean you input data.

For example convert key fields in your DIM and FACT tables to uppper case, use CLEAN to remove sepcial characters and TRIM to remove trailing spaces.  You can also remove duplicate keys from your DIim tables in 1:M relationship

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[Meep]

How is anything that you said relevant to my post? 

[speedramps]

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.

[Meep]

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. 

[speedramps]

Thank you @Meep 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.