Power Pivot relationships

0
votes

Trying to create relationships (joins) between tables in power pivot.

Got 2 tables I wold like to join together, connected with a common column = CustomerID. One is a Fact Table the other Dim table (look up). I have run the "remove duplicates" on both tables without any problem.

But I still get an error saying : "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".

The Fact Table contains duplicates (as it should?) and the Dim Table do not, why do I get this error?

Help much appreciated

2
powerpivotpowerbi
Every time I've run into this and was sure there weren't any duplicates in the lookup key of my lookup table, it turned out I was wrong. Assuming you've triple-checked and found nothing, the next step I'd take is to limit the rows in one or the other of the tables until you can get it to work, and then add the missing rows back in until you can identify rows which are causing the error (from where the reason why they're a problem would hopefully become painfully obvious). – Leonard
Thank you for your answer, will look into that. – Alexander Johansson

2 Answers

0
votes

Created an appended table with both columns "CustomerID". After the columns where appended together I could "remove duplicates" and connect the tables together through the newly created appended table.

Don't know if this causes another problem later however.

0
votes

You can also check for duplicate id values in a column by using the group by feature.

Remove all columns except ID, add a column that consists only of the number 1.

Group by ID, summing the content of the added column and filter out IDs whose total equals 1. What's left are duplicated IDs.