I’m creating a model from Data in Salesforce and just found that as many of the Key Columns are Case Sensitive, I cannot create a relationship between my Opportunity Table and Accounts Tables, using the Account ID from the Opportunity Table as a *:1 releationship in PowerBI complains about duplicates when there are none.
Opportunity Table
Columns = Opportunity ID, Account ID
006470000014gB, 0015000000KCouT
0064700000a5vd, 0015000000KCout
Account Table
Columns = Account ID, Account Name
0015000000KCouT, Account B
0015000000KCout, Account A
Any way to get around this so I can create the relationship, otherwise I will have to add the data I need directly to the Opportunity Table as additional columns, which I would like to avoid, before I load the data into PowerBI.
Thank you
Neville
Hi Neville,
Am I missing something. The account ID columns look exactly the same for the two example you’ve given there
If this is the actual problem have you tried solving in the query editor? You can use the ‘column from examples’ features and attempt to normalize all the values in the columns across the two tables.
Sam
Sorry I just re-read and saw the slight difference.
Really, is that how the data differentiates between different accounts? That’s crazy.
Ok, I’m going to do a bit of testing on this tomorrow. I’ll come back to you.
Are the two different ID actually different customers or account.
You can certainly use the column from examples feature to turn everything lower case if that works.
As per below
If you need something else, will have to explore further
Hi Sam, thank you for taking the time to respond.
I cannot change the case of the Account ID as this is case sensitive and needs to be maintained in order to create the relationships.
What I’ve done to address this in Power BI is …
- Load All Accounts and then add an Index Column
- Merge the Index Column into the tables that contain the Account ID (Merge is Case Aware)
- Use the newly created Index Column to create the relationship I need.
This is the only why I found to do this, until Microsoft enables Case Aware Relationships.
Thank you
Neville
