HR Dataset from multiple CSV files

[Anonymous]

Hello,

I have 3 csv files, one showing overall headcount, another showing just all new hires and the last showing all leavers. For each csv file, an employee ID/employee reoccurs on multiple rows for each month they have been with the business. The HC file most of the columns in the starter and leaver files but not all; it does not, as an example contain a termination date. I have brought all three files into PBI desktop to build a singular report but having issue with the relationship as I am trying to connect the HC file to both new hire file and leaver file using employee id but what results is a many to one cardinality which prevents me from getting an accurate headcount as employees are counted multiple times. My question;

I ideally want to merge all 3 files into one main HR data set (this would make getting attrition data easier!) and have distinct count for each employee i.e one start date and one leave date where this applies (i understand i will still have multiple rows for each month they have been with the business). Is this possible or would it be better trying to manage the relationship (I would appreciate some help with either of these options)

[Icey]

Hi @Anonymous ,

If you don't mind, please share me some sample data without real data or sensitive information. And I'll conduct specific tests.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @Anonymous ,

Believe that your best option is to make a Dimension table with the ID of the employee to make the relationship between the 3 tables that way and using this table on your visual should give you only one value per count.

If you want to merge all the table into one you need to use the Query Editor with the Table.Combine, check the post below with a full explanation of the join types and how to do it.

https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

However be aware that using this type of aproach you will then probably need to have a distinct count instead of a count of your data.

Regards,

MFelix

[Anonymous]

Thanks for your reply.

I did try to create a Dimensions Table using employee ID with the thinking that this would link both tables and create a relationship allowing me to cross select values between both tables. This did now however help as when I select data from the first table and another from the second table, I got an error message and no visual was displayed. Could I be doing it wrong?

The relationship between the heads table and the Starter and Leaver table is such that the heads table has multiple instances/rows of employees for every month they have been with the business since April 2018 while the latter two have just one instance i.e. when they joined (starter table) or left (leaver table) so ideally, the heads table should contain everyone in the starters and leavers table, the starters should contain everyone in the leavers table and the leavers would only contain instances of the heads or starters if they have left.