Relationship in power bi

[HowardFrazier76]

I have 2 tables that I want to create a relationship between. The first table is the employee roster from Sql Server it has the employee name , employee number , hire date and termination date. The other table is the training roster comes from the share point spreadsheet maintained by several people it contains employee name , training date , employee #. I'm trying to create relationship with employee number but I keep getting the message that I can't create a one to one it has to be one to many. I get this message even after I got rid of the duplicates from the spreadsheet. I also changed both to whole numbers. It's it because I have to trim the space in the cells first ?  What am I doing wrong ?

[rbriga · Accepted solution]

Here's a quick trick for that-

Create a table in PBI (or a pivot table in Excel), insert the key you wish (employee #) and a distinct count of employee # for each. Sort descending by the distinct count.

If you have duplicates, they would float to the top.

Also, clean up the employee # by turning it into a string\ varchar and trim it of spaces.

IDs aren't numbers you add, multiply or average over, so they don't need to be integers.

[tamerj1]

Hi @HowardFrazier76 

you might also check for blanks