Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rorromartins
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
Resolver II
Resolver II

@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?

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.

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

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.

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
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.