PP - r/ship can be created because each column contains duplicate values

[KimC2504]

Hi, I have used power pivots quite successfully and I am now in the process of setting up a new model with new tables. I have a master list of accounts with a unique id number. I am then adding in an actuals table and a budget table. The budget table is linking fine to the master list table via the unique id number however the actuals table will not. I keep getting the error message "The relationship cannot be created because each column contains duplicate values." Now I know the master account table is unique as the budget table links to it fine. I have tried a subset of the data and that worked fine however when I try to use the full data it won't work. Does anyone have any suggestions on how to trouble shoot this problem?

 

[RickXL]

Hi,

Why not asdd something like this to the worksheet you want to check?

=COUNTIF(A:A,A2)

If you drag that down to each row it will check how many times each value in column A occurs. You can then filter out all the single occurrences with the AutoFilter. What is left will be duplicates.

 

[scottsen]

hmmmmmm. I will say that I have never seen that error "lie". If it says you have dupes, I have always had dupes. That budgets linked is interesting. However... is it possible the budgets linked in the OPPOSITE direction? and it too has issues?

I would write a measure:
= COUNTROWS(MasterAccounts)
then drop your account id's on rows of a pivot and the new measure on values. Then you can easily drop the filter and eliminate the "1" columns that you expect... and be left w/ the dupes.

Typically those are BLANK() values actually.

 

[KimC2504]

The majority of the unique ID's started with AU or NZ but a few where # or ?. I think the mix of formatting was causing the problem. I have made all unique ID's start with an alpha prefix and that seems to have solved the problem. Thanks for the suggestions.