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

[KSveigaard]

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

[fl-330_Studio]

Hello,

For me the origin of the problem was a duplicate excel file.

I made a backup of the source Excel file in a different folder, but PowerBi used to import twice the data when refreshing the Dataset.

Hope this helps anyone

[NghaNdze]

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

[StephG70]

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 

[AgnieszkaFN]

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! 

[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])

[Anonymous]

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"})

[jweinapple]

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!!!!

[thegeo]

I wanted to share my experience with this error in case it can help someone wandering in from Google.  If you know for a fact that at least one field in question is unique in your source data, this may help.

I imported my data from Excel too. All of it was summary / detail with the summary data on one worksheet and the corresponding detail data on the other. Each summary dataset had a summary key field that was unique and had a one to many relationship with a particular field in the corresponding detail data. 

Power BI has never been able to figure this relationship out, so I have to draw the relationships by hand for this particular data. For one of my summary / detail pairs, I received this error. I verified the summary data in Excel was unique.

I tried wiping out the summary and detail tables and reimporting them. I had errors upon import because Power BI is also not good at guessing the types in my data. I saw that it had formatted both my summary key fields as numbers when they needed to be text. I did a Change Type and declared them text. I still could not establish the relationship.

Then I decided to dig into the data in the data model, starting with the summary table, which I knew had the unique summary key field. I scrolled all the way to the bottom where I saw there were still error messages in that field. The errors declare that these particular values couldn't be converted to number. Despite putting in transforms to make that field text and the little icon in the field name indicating it was a text field, Power BI was still trying to make it a number.

I went into the Advanced Editor (Edit Queries --> Right click offending table) and declared the field text. That finally fixed it.

Again, just wanted to share. 😉

[Jase71ds]

Sharing my resolution with this problem...

Sorted first column of table in Ascending order (as someone suggested). Sure enough, I had a blank row now showing at the top.

Went back to my data source (Excel table) and found that it was no-longer showing as a Table (I have no idea why) so I made it a table again. Then I checked using the filters - there were NO BLANK ROWS. Great!

Went back to BI, refreshed data, sorted Ascending - d@mmit - the blank was still there!

Here's the solution that worked for me.

I went back to my Excel Table, and I selected all the Rows beneath the last row of table data. I then deleted these rows.

(I did the same for the columns to the right of my last column).

Saved the table.

Went back to BI - refreshed data.

Sorted first column in Ascending. Halleluja, no blank row.

Now I was able to make the data relationship.

[mshodge]

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.

[jweinapple]

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!!!!

[jteccampbell]

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!

[Anonymous]

I am having the same issue but the solutions offered on this page are not solving the problem. Any other suggestions?

In spreadsheet 1 my column is: Account Name

In spreadsheet 2 my column is: Account Name

There are not duplicates, nor are there any blanks.

[JoaoMachado]

I have the exact same issue. No redundant data, no null rows... Thanks in advance for whoever may help us!

[MassimoSolcia]

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)

[adamwaugh101]

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!

[anguyen83]

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?

[ankitpatira · Accepted solution]

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

[aybarsacar]

Hi,

I am getting the same error even though the table i have brought in has unique values as I am using the primary key column guid in the database

Unfortunately, I can establish a 1-many relationship on power bi even though the relationship exists in the db...

do you know what may cause this?

thanks,

[NghaNdze]

Hello,
I had the same problem too and this is how I solved it.

Power query is case sensitive but it seems power bi is not. This implies if you have the same word written in different casing, power query will detect the values as unique while power bi will detect it as duplicate. There, apply CLEAN, TRIM, and one of the case transformations(upper, lower, sentence case) to both id fields of the two tables. 
This will help you find the duplicate value in one of your queries that is supposed to contain unique values. You can remove duplicate from the dimension table after applying the above transformations.