Forum Discussion
Can´t create a relationship between two columns because one of the columns must have unique value
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.
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. ;)
Advocate ISharing 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.