Creating a Relationship Error

[RossBateman]

I have two tables with an invoice number being the unique field in both. When trying to create it comes up with the error message of ‘You can’t create a relationship between these two columns because one of the columns must have unique values’. 

I've removed duplicate rows on both tables but the error message still comes up.

Has anyone had this problem before and if so would you be able to help?

Many thanks 

[amitchandak]

Create a visual table or matrix, take invoice number and again take invoice and change aggregation to count.

Sort desc on Count of Invoice no. Make sure there is nothing having count more than 1

[RossBateman]

Thanks, it looks like this is the problem. The remove duplicate function doesnt seem to be romoving all of the rows! As I've got over 55k rows do you know of any other ways to delete duplicates?

[v-juanli-msft]

Hi @RossBateman 

As tested, "remove duplicates" works on my side.

Please share some sample data for me to test and find a solution for you.

Best Regards

Maggie

[RossBateman]

Thanks Maggie. The problem was that some of the invoices were different but werent classed as unique when trying to set up the relationship. For example there was invoice number 003 and 0000003. The remove duplicates didnt class those as the same but when not in the query mode they were classed as the same. 

The only way I think I can get around this if I add a letter to the begining of every invoice.

[v-juanli-msft · Accepted solution]

Hi @RossBateman 

As a workaround, If the column has only number type value, you can change its type from "text" to "number",

Then add "prefix",

Then remove duplicates works.

Best Regards

Maggie

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

[HuiYin]

Need help!

I have the same issue when creating the relationship between the tables. 

I have 3 Facts tables:

Table 1: Have columns of "Acad Session", "Modules" and "hours per week for the module"

Table 2: Have columns of "Acad Session",  "Teaching staff" , and "modules staff are teaching"

Table 3: Have columns of "Acad Session",  "Teaching staff" , and "Status, (Active or Retired)" 

Dimension tables for modules are created by duplicating the Table 1 and keep the column  "Modules" first and then remove the duplicate in the column. But I received the error message "You cann't create a relationship between two columns because one of the columns must have unique values" when trying to create the relationship with Table 1.

Same error for creating Dimension table for "Teaching Staff"

Dimension table for Acad Session and Status have no issue (these two dimension tables have few rows only)

Thank you in advance for the advice/help