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
KSveigaard
Regular Visitor

Can´t create a relationship between two columns because one of the columns must have unique value

Hi 

 

In Desktop Power BI, I have made a report, with 3 excel files, and want to make a link between these excel files,

 

In file A: I have the "poduct number", and lots of other product information (ig. type, format)

In file B: "product number", "sales date" and lots of sales data. (ig. units sold, units inventory) (Customer I)

 

In file C: "product number", "sales date" and lots of sales data. (ig. units sold, units inventory) (Customer II)

 

When linking / relationship - the "product number" I get this error message:

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

Check with vlookup, in the excel´s files and there it is possible to find, the product number both ways. 

All product numbers are formatted to number, so it should work - but dosent ???

 

1 ACCEPTED SOLUTION
ankitpatira
Community Champion
Community Champion

@KSveigaard basically what that error is complaining about is that relationship you're trying to create from column within 'FROM' table to your column in 'TO' table has issue. one of that column must have unique product numbers. you can't have Many to Many relation but only One to Many or Many to One relation. So ensure that one of the table has unique product numbers and then there won't be any issue. 

View solution in original post

36 REPLIES 36

Great well constructed explanation.

I'm sure that 'From' table column value is unique. But I still got this error. Any suggestion?

Hi

Am also new with PBI .I tried To make relationship between two tables like In one table I have Branche name and branch number With Current Year Sales and other elements and in  other table I have same Branch name and branch number For target sales when I tried to compare I got the same error that I have some Unique Values.

thanks

About the unique values: I thought me too. Go to in the Data View and order ascending the column, nulls should appear on TOP.

I had the same problem, turned out I had a blank row at the bottom of the table which was giving out a reading of 'null' and stopped the relationship working.

 

I deleted the null row and it work after that, although it only worked when I dragged the relationship one way round and not the other so try setting up the relationship both ways if the first one doesn't work.  Not sure why this would be though

Anonymous
Not applicable

Hoooray.. getting null out of the column solved the issue for me. You are a savior my friend 🙂

Having a 'null'  value also causes this issue.  If you can delete the 'null' values, all good, but if your dataset indeed have 'null' values you will need to find a way around that.  I have used replace to replace 'nulls' with a value, and that solved the problem quite nicely.

My data source is from mysql and the field that I use to create relation is primary key of this table. I'm sure that there is no blank row in this table.

 

I'm stuck with this for a week and still have no solutions for this problem. poor me

I am confident table From has unique values too (it only has nine rows) but still getting this message.

I am having hte same issue, but I do not understand what you mean by unique values,

Table A contains the name of a Sales Person and their Manager

Table B Contains the name of a Sales Person and their assigned Companies.

 

Why can't I connect the two tables on the sales person name so I can create a Matrix that shows the Manager name, sales person name and the sales person companies?

Unique means, that the sales person on one of the both tables should be not be double (or more) in several rows.
So check, if the sales person in the first table is only one in the table!

what if in some cases you have header and lines situation. meaning a table captures information at header level with a unique id and another table captures information at line level with stamping the header id for each line to retrieve info in a single query. How do I manage one to many as I could have duplicated ids in one table based on design? please advise.

I got my data from Excel.   I'm sure that there is no blank and no duplicated row in this table, and order ascending the column and there are no nulls on TOP. Additionally, I used the option eliminate duplicates in Power BI and after that the option eliminates blanks. But I wasn’t able to make the data relationship.

I checked in PowerBI Data View the data table and I have this:  “TABLE: table name (5.946 rows) COLUMN: column name (5.945 distinct values)”

 

I don’t understand why I can’t make the data relationship. Can anybody help me? 

 

I am having the same issue, I'm pull data from an R script where I state only to select unique non-null values and then have added a step in Power Query to replace nulls and blacks with "ISSUE ROW!" and then also remove duplicate rows - However I still cannot define the relationship

Thanks.  I have been so frustrated with this.  I just went back and looked at my lookup table and a row got duplicated!  It now works!! 

Lesson learned 🙂

 

Thanks!!

Thanks. Got it now

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.