PowerPivot - relationship cannot be created because each column contains duplicate values

[danvilla2]

Hi


This is probably a very basic problem but forgive me, I'm new to PP. I have table 1: SKU, Size and Table 2: Sku, Volume.

When I link the tables I get the error that the relationship cannot be created. Table 1 only has unique SKU's in it, so I'm confused as to why it can't create the link. I understand the basic principles of Access but this is doing my head in!

Thanks

 

[TimRodman]

Maybe you have more than one blank? Did you try pasting the column into Excel and using the "remove duplicates" feature to make sure that there aren't any duplicates?

 

[scottsen]

Ya, we get this question fairly frequently and my answer now is the same... I have *never* seen Excel lie about this. You have duplicates. And as Tim points out... the most common mistake here is multiple blanks.

I like to throw sku on rows, and a =COUNTROWS(Table) measure in values, sort by the count desc... and see my mistake at the top.

 

[Notrump]

Ya, we get this question fairly frequently and my answer now is the same... I have *never* seen Excel lie about this. You have duplicates. And as Tim points out... the most common mistake here is multiple blanks.

I like to throw sku on rows, and a =COUNTROWS(Table) measure in values, sort by the count desc... and see my mistake at the top.

I have two tables in Access with a one to many relationship. They have primary keys on the tables. For the many side I created a view and put the three parts of the composite key in a field. The one side table is joining on it's PK. There are no nulls and no blanks.

I Created a pivot table in excel using the PK field (customer ID) with count as a value. All values are one. Given all this I am still getting the dreaded "can't create key because duplicates in both tables".

To be sure I queried the table in Access and got a count. Next I create a query with a join, same count. Finally I created an outer join on the many table, same count. It might never happen, but I can't resolve the issue .

 

[ImkeF]

Very strange. All I can think of here is case sensitivity. But as all these different programs should not be case sensitive they should turn out the same. ...
Anyway - I'd do the following steps:

1) Check for duplicates in Excel - and if this doesn't return the error
2) Create new keys using Power Query: Create a Dimension Table with Power Query: Avoid the case sensitivity bug! – The BIccountant

Then you should have at least one row more - and can identify the reason by checking for duplicates in the old key-column.

 

[meileetan]

I did a =countif formula to check for duplicates, even used conditional formatting, but did not get any. My column does contain cells that are numeric and alphanunmeric, i.e. inconsistent. Would this have made a difference?

 

[awier]

I had a similar problem and eventually found a value with a trailing space. There was an identical entry without the trailing space. After deleting the one with the trailing space i could create the relationship.

HTH-Anneke