Creating Case Sensitive Table Relationships

[benji]

I am having trouble making a relationship between two tables. Due to case insensitivity, powerBI thinks that there are repeat entries on both tables and so it will not create the relationship.  

Specifically, I am using Salesforce.com data and trying to link my accounts table to my opportunities table based on AccountID. It just so happens that several AccountID's are identical except for case; i.e., one account ends in "ef5Hi" and on account ends in "Ef5hi"

Is there a way to get around this?

[SqlJason · Accepted solution]

This case has been discussed here - https://social.technet.microsoft.com/Forums/en-US/f12496b0-7424-44e7-a384-a08694595899/power-query-l...

The recommended solution was to create a dimension table as @greggyb suggested - http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/

[benji]

Do I need to use Power Query to accomplish this or is there a way to do it all within PowerBI?

[SqlJason]

You can edit the queries within Power BI and do the same operations (the similar feature in Excel is called Power Query, and is very similar in look and feel).

[greggyb]

In Power Query you can add an index field to your lookup table, and also merge that index into the fact table.