Dealing with duplicates in my data

[shophoney]

Hi I have a list of employees coming from my payroll program. Unfortunately the file pulls out each employee and each costing location you can work at. I'm trying to link the employee table to the sales table. But it says both sides have a many.

What would be the best way to have a ONE TO MANY. Should I take the data each week and run VBA to remove the duplicates. Or can i write a powerpivot formula to deal with it.

So Table1 is my employees and it lists you many times as you can work at each location (no control on getting the list any different). Table 2 is the sales table listing all your sales for each cycle.

Thoughts?

 

[XLfriend777]

Hi shophoney - You might consider creating a VBA script that eliminates the duplicates in Table1. I can't see any reason you would want an employee listed twice in a list of employees.

 

[macfuller]

This sounds similar to our hospitals where nurses can work at multiple locations and thus have several HR records showing their different pay rates, supervisors etc.

Assuming you are using PowerQuery to load your data to the model, extract your HR data into a lookup table. Create a separate query, load the HR file, and eliminate duplicates (you may want to keep other columns of the data such as SSN, original hire date, etc.) Then link the employee lookup table to the HR and sales table. That way you preserve the HR multiple row data in case you want to calculate average pay across locations or something like that.