Relationship with Unique Value

[tstackhouse]

I am trying to create a relationship between two tables. I have tried to do this and I have been receiving the error "cant create a relationship between these two columns because one of the columns must have unique values." I have removed duplicates in the query editor and I am still receiving this error. I have also tried to remove errors and neither seems to work.

Any suggestions??

[vanessafvg]

@tstackhouse

1. are they all the same case?

2. do you have any blank values?

power query  sees different cases as unique values, dax doesn't.  hence remove duplicates no working

also if there are blanks on the side that should be unique you need to pad them with a value like unknown or -1 or something similar.

[tstackhouse]

I have one row that contains a blank value, How would I go  about padding this with a 1? I have tried to just create a new column by using:

=if([Master Column]="",1,[Master Column].

With this I am recieving an "Expressions that yield a varient data-type cannot be used to define calculated columns" error. 

[vanessafvg]

@tstackhouse IF ( ISBLANK ( [Master Column] ), 1, [Master Column] )

[tstackhouse]

The formula returns the same error that I was returned previously for me.

[v-chuncz-msft · Accepted solution]

@tstackhouse,

Enclose 1 with double quotes (").

Column =
IF ( [Master Column] = "", "1", [Master Column] )

[tstackhouse]

Thank you that one works for me!

[tstackhouse]

I have one row that contains a blank value, How would I go  about padding this with a 1? I have tried to just create a new column by using:

=if([Master Column]="",1,[Master Column].

With this I am recieving an "Expressions that yield a varient data-type cannot be used to define calculated columns" error.