Forum Discussion
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 ???
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.
37 Replies
- ankitpatira
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.
AtirajAdvocate I
I'm sure that 'From' table column value is unique. But I still got this error. Any suggestion?
HowiecoolwayI 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
Thanks. Got it now
LMormanHelper I
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?
- TristanKuesters
Resolver I
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!
Hi all
I've been struggling with the same issue for the last 2 hours and found the solution in my case :
I discovered that Power BI removes spaces at the end of a text string when importing from Excel
This is why 2 values that were distinct in my Excel table (because of this extra " " at the end of the string) were not unique according to Power BI after the import
Good to know
You just saved my day! I was going crazy already with this... couldn't find the duplicates.... and then I saw your comment! THANK YOU! the strange thing is - Power BI identiefied those as a duplicate (couldn't create the 1-many relationship) but if I deleted the duplicates (in Power Query) it didn't delete those... don't relly understand how it works.... but anyway - THANK YOU VERY MUCH!
This one was doing my head in but as it turns out there were two null rows that I hadn't deleted!!! Check your tables/queries for empty values!
JoaoMachadoI have the exact same issue. No redundant data, no null rows... Thanks in advance for whoever may help us!
Same here, but I got it: in the Excel the table has no NULL, but when loaded in PowerBI I found rows with NULL.
Go to in the Data View and order ascending the column, nulls should appear on TOP.
Cleared the NULLs, I'm able to create the relationship.
Hope it helps,
Massimo
PowerBI Desktop Version 2.46.4732.721 32-bit (may 2017)
I was struggling with this and GOOGLE SHEETS.
In PowerBI Data View I would highlight each column header. On the bottom it would say
TABLE: table name (148 rows) COLUMN: column name (22 distinct values)
For BOTH tables it would say 22 distinct values but still it would not create a relationship. Within the data my 1 of *(many) column is created from a query of the * so there should not have been a problem.
I tried adding a NULL into the 1 side of the column to see if that worked. It did not.
Finally within the source data I created a tab solely for the 1 side. It has no other purpose than to conduct the query to create the 1 list. This worked. Putting the data by itself may have had nothing to do with it but when I did this it suddenly was able to create the relationship.
ANSWER!!! This is a good response that got me thinking.
If you are still struggling after making this change, MAKE SURE YOU DO NOT HAVE CASE SENSITIVE MATCHES!!!!!!
I was able to find duplicate values such as a cell containing "A_Weid" and "a_weid".
POWER BI READS THESE AS SEPARATE VALUES!!!!
ANSWER!!! MAKE SURE YOU DO NOT HAVE CASE SENSITIVE MATCHES!!!!!!
I was able to find duplicate values such as a cell containing "A_Weid" and "a_weid".
POWER BI READS THESE AS SEPARATE VALUES!!!!
- Polly
I still can't see how my values are not distinct but I have managed to get this to work by creating a calculated table and creating the relationship from there
Lookuptable = DISTINCT(table[column])
In case it's helpful to anyone...
Today I realized that the Filter funtion inM/Power Query doesn't easily combine AND and OR statements. My issue resolved when I separated this into it's own query step (I was trying to build a relationship on a field called [TUID__c]:
#"FilterTUID"= Table.SelectRows(Contact1, each [TUID__c] <> "" and [TUID__c] <> null),
It was previously part of one big filter step with this other filtering, which obviously doesn't work, because the OR statement just works on whatever it's next to:
#"FilterUnit"= Table.SelectRows(FilterTUID, each [Unit__c] = "CURRENT" or [Unit__c] = "ALUM"),I also learned that removing duplicates alone will not kill a null value, and that the null value will still generate the "duplicates" error when creating a relationship (as weird and stupid as that sounds - it basically doesn't count the null as a distinct value, yet it counts it as a value, or something...). So, if you have a single null, this query step is useless:
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"TUID__c"})
Hello,
Power query is case sensitive, it shows you that all records in a particular field are unique yet you cannot create relationships between two columns because of many to many relationships in power bi, which means that your data is probably "clean enough".Here is a work around,
Transform the ID columns in the same way; E.G. you can apply TRIM, CLEAN AND UPPER CASE/LOWER CASE/PROPER CASE to both ID columns then REMOVE DUPLICATES.
Hopefully you will find the duplicate values.
Thanks
Hi
I am new to powerBI. I've spent years using MS Access and now moving into this system is all foreign to me.
I have two tables which I am trying to link
Date from activities2 table: Date Table which format is (dd mmmm yyyy)
tbl_Date from tbl_dates table: Date Table which format is (dd mmmm yyyy)
I want to link both tables and return the field called dt_Friday_WE which is a marker that groups the date field into categories
But I am getting the same error. If I was using access then i am able to do a 1:1 join. However PowerBI works differently
Can someone help?
Having the same issue here. I have no duplicates, no blanks, and no null values.
I am wondering if having similar unique identifiers is whats causing my issue. For example, I have an 00 identifier and a 0000 identifier. Will this cause the same issue??
So frustrated right now!
