cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rorromartins Frequent Visitor
Frequent Visitor

You can´t create a relationship unique values with non duplicates

Hi everyone! I really hope someone can help me solve this issue as it's starting to drive me crazy.

 

I'm trying to create a relationship between two tables, one of them a historic database with a list of writings featuring specific products (products are called Ends) (Table Base-Ends), and the other one is a list of all the available products (no duplicates) with some different categories for each one (Table Dict Ends and Families).

Base-Ends is already related to another table by field "Writing ID", and Dict Ends and Families is not related to any table.

 

I want to create a relationship based on the field "End Featured"/"End" in each table (text fields in each of them), but at the moment of creating the relationship, i get the following error message:

 

"You can´t create a relationship between these two columns because one of the columns must have unique values."

 

Things I have already taken into consideration:

 

  1. Transforming to uppercase in "Dict Ends and Families" table and deleting duplicates and blanks through the Query Editor.
  2. Making sure existing relationship to Base-Ends is in single direction.
  3. I have already tried creating a new table to act as an intermediate table (Ends List) to create a relationship to the other 2 tables in the query editor by duplicating the Dictionary, removing all the columns but the "End" field, summarizing it and counting rows to make sure there are effectively no duplicate values, removing blank rows and duplicates also to make sure there are no duplicates.

But I still get the same error message.

 

A strange thing I notice in the original dictionary is that after applying changes from the Query Editor, in Data view columns are re-arranged, puting a field wich does contain duplicates in the first place. Does this have something to do?

 

I woul REALLY appreciate if someone could help me as I've already lost a lot of time trying to solve this and navigating through different forums to find an answer, being unsuccesful.

 

5 REPLIES 5
Jorgast Regular Visitor
Regular Visitor

Re: You can´t create a relationship unique values with non duplicates

@rorromartins

 

I have run into this frequently when joining my tables. What this message is trying to tell us is that there are duplicates "End" in both tables (Many to Many relationship) and 1 of those tables has to have a unique value, of just 1 "End". Basd on what you have provided, does 1 Table Base-Ends show up in Table Dict Ends and Families? Is their another unique key in both tables that does not have any duplicates, that you can use to join the 2 tables together?

rorromartins Frequent Visitor
Frequent Visitor

Re: You can´t create a relationship unique values with non duplicates

The Table Dict Ends and Families should have unique values for each end, but I have checked and there's something weird going on with those ends with two spaces between words, or those ends with a space at the end of the name. Remove duplicates is not recognizing them as duplicates, so it doesn't remove them, but table relationship sees them as duplicate values.

Jorgast Regular Visitor
Regular Visitor

Re: You can´t create a relationship unique values with non duplicates

Is there an Account# or another unique key whichs shows up once (i.e. SSN or Driver License #, or an ID#?)

rorromartins Frequent Visitor
Frequent Visitor

Re: You can´t create a relationship unique values with non duplicates

No, there isn't one. The base is generated from manual inputs from different users which not always upload information correctly (E.G. double spaces between words or at the end of the product name), and the dictionary corrects that automatically, that's why I want to connect both tables.

Community Support Team
Community Support Team

Re: You can´t create a relationship unique values with non duplicates

HI @rorromartins,

 

Maybe you can create a calculate table to store distinct value.

 

Sample:

Distict ID =
DISTINCT ( UNION ( VALUES ( Table1[Column] ), VALUES ( Table2[Column] ) ) )

Then use above table as bridge table to link original tables.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |