Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
HowardFrazier76
Frequent Visitor

Relationship in power bi

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 ?

1 ACCEPTED SOLUTION
rbriga
Impactful Individual
Impactful Individual

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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!

View solution in original post

2 REPLIES 2
rbriga
Impactful Individual
Impactful Individual

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.

-------------------------
Data analyst by day, hockey goalie by night.
Did I help? Then please hit that "kudos" or "accept as a solution" button!
tamerj1
Super User
Super User

Hi @HowardFrazier76 

you might also check for blanks

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors