Forum Discussion
Different capitalization in Power Query and loaded data affecting calculated columns
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,
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/
7 Replies
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/
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.
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.
