cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BruceRudd Frequent Visitor
Frequent Visitor

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

So I did search for this and didn't find any help.  Just to start of, I understand what this error message means and why it is in place.  However, I think it is erroneous.  Since my data starts in Excel files I started examinging the column there.  I used conditional formating to highlight what Excel thinks are duplicates and they are not.  The first 15 digits are the same but not the last 4.  I did find that Excel has a limitation at 15 digits so I change the colum type to text and it still highlights them as duplicate.  I also edited my query in Power BI to make the text and no relief from the problem.  This is a set of values that Excel things are duplicate.  I have sorted this column so duplicates would appear next to each other.

 

201811126200120039
201811126200120061
201811126200120076
201811126200129004
201811126200129011
201811126200129083
201811126200129124

 

Any insights on how I can get Power BI to recognize these values as unique?

 

Thanks,

Bruce

8 REPLIES 8
Super User
Super User

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

@BruceRudd do you have any blank/empty value in your dataset. If it is then you will not able to set relationship on this column.

 




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






tarunsingla Member
Member

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

Try this:

In the Query Editor, select your dataset (top left corner of the data table), and add a final step (Remove Duplicates) to the dataset.

RemoveDuplicates.png

BruceRudd Frequent Visitor
Frequent Visitor

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

Good question.  These are supprt case number so their shouldn't be any blanks but that does not mean their aren't.  I'll check it out.

BruceRudd Frequent Visitor
Frequent Visitor

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

Thanks for the reply but since it appears that these are erroneous errors, I don't want to delete the false positive duplicates.

BruceRudd Frequent Visitor
Frequent Visitor

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

Does anyone have any thoughts on how to get around the limitation of 15 characters in unique fields.

tarunsingla Member
Member

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

Is the data in Excel file unique. If it is not, then it needs to be fixed in Excel first. I tried to copy paste the numbers you provided into an excel file and had to use text import wizard to paste the numbers as text. And only then I could get the unique numbers.

 

Could you share the excel file that you are using as a data source for Power BI.

Highlighted
BruceRudd Frequent Visitor
Frequent Visitor

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

Yes, they are unique.  I had tried change the data type of the column to text thinking that would help but it didn't seem to affect anything.  Maybe the cut and paste is needed to convert it.  Unfortunatly, I can't share the full file as it very large and propiretary.

Super User
Super User

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

@BruceRudd so there are blank/null value in the column?




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin