Unique column values

[dgenatossio]

I have read through dozens of posts and haven't found a post with an answer for the problem I'm encountering.

I have two data tables. One has Plan for the Year. The other has Results. I want to connect them using ID codes, which are a seven digit number.

I am very familiar with unique values and most of Power BI's functionality, but the error isn't making any sense.

I used reference to create a new Plan table and removed the other columns so all that was left was the ID codes. Then I used reference to create a new Results table and removed the other columns so only the ID column was left.

So to be perfectly clear, at this point I have two tables. One has a set of ID codes for Plan. The Other has a set of ID codes for Results. 

I then appended the tables as a new table, giving me one big list of Employee ID codes. Then I removed duplicates.

Now, I try and create a relationship between the original Results table and my new ID table. It gives me the unique values error. Same for when I try to connect the Plan table.

It doesn't make any sense to me. I have tried removing duplicates before appending tables, and then again when I append the tables. The ID columns are the same data type. Nothing seems to be off.

Keep in mind that it lets me create a relationship between the Plan table and the Plan ID table reference. I am also able to create a relationship between the Results table and the Results ID table.

Anyone have any idea what could be causing the error?

[ChrisSschmucekr]

Try remove empty rows in Edit Queries.

[Eric_Zhang · Accepted solution]

@dgenatossio

One way through DAX. Create the employee list as below. If it is not your case, please post some sample data and expected output.

employees =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( plan, "empid", plan[Employee ID] ),
        SELECTCOLUMNS ( result, "empid2", result[Employee ID] )
    )
)

[dgenatossio]

Thanks, this worked perfectly. And could you also tell me how to create a table with multiple columns? Say the employee's home state for example, which could be found on either the results table of the plan table. Is there a way to bring that data over as well?