Even if there is no duplicated records, Power BI does not create relationships between two tables

[FreonYz]

Hi ,

I have really tested in quite a few ways before the table uploaded to my power bi desktop, and made sure there is not duplicated records in at least one column, but Power BI still does not allow to create relationship between my two tables and keeps warning me "You cannot create a relationship between these two colunms because one of the comumns must have unique values" .

My case is that, I have my unique unit description in my Unit Dimension table and unit purchase record in my transaction table. I wanted to join these two tables, in order to directly look up the unit dimensions from the unit purchase records in my transaction table.

Hope someone can give me some hints to solve this problem. Highly appreciated!

Thanks!

[parry2k]

it could be that you master table has blank/null value, sometime it gives this error message because of that. please check that.

[FreonYz]

Hi @parry2k

I forgot to mention that, actually the two columns o which I would want to create relations based upon does not have blank/null cells.

In addition, I did some test on have a column with unique values to join with:

1. Another column which only also contains unique currency code(no blank/null), 

2. Another column which contains duplicated currency code but no blank/null-----Currency 2 in the screenshot

3. Another column which contains blank cell------Currency 3 in the screenshot

It turned out that, Power BI only allowed me to create relation between tables in #1, not #2 or #3. Why is that?

[Anonymous]

I have the identical error. Even when I copy and paste data straight into a new table. Really hard sorting a chart of accounts!!!
If you get anywhere with this issue please post the solution as I would love to know how to fix this bug like behaviour

[parry2k]

make sure in master table there is no blank/null value in a field you are setting relation to

[Anonymous]

What exactly do you mean by 'master' table??

[parry2k]

for example, you have two table, customer and sales order and you are setting up relationship on customer id between sales order and customer.

In this case customer is a master table and if there is a null/blank value in customer id field of customer table then you will get the same error as you are seeing. 

So make sure in you scenario whatever you are setting relation to, doesn't have null value, clear?

[Anonymous]

Stupidly I have misread this thread...

What I thought this was regarding sort order of a table.... sorry everyone 🙂

I keep on getting this error message while trying to sort a column by another column...

"We can't sort the 'subheader' column by 'accountKey'. There can't be more than one value in 'accountKey' for the same value in 'SubHeader' Please choose a different column for sorting or update the data in 'accountKey'

However, accountKey has no duplicates (which is where my head crossed wires about the topic of my thread)