Parent Child Hierarchy Problem

[patkang]

Dear Community 

I am stuck with a case, and hope that somebody may be able to help out as I always found an answer in this wonderful community!

This is regarding a organization structure, there is a list of departments, with their respective parent department. 

 

When flattening out the structure and displaying the department names and the employees in the department it looks the following:

 

The first five columns are the names of the department and end at the respective department of the row. The sixth and seventh column is the department id and the respective parent id. 

The last three columns are the job title, employee id and whether this is the employees' main employment. Since this is a very small company, one employee has more than one job. In this case there are 3 duplicate entries, where the head of the department is also a worker in the child department. 

The issue that I am having is, that every employee can only have one entry in this list. Therefore, i need to remove the duplicates in the employee id column. I sorted first the emplooyee id, so that the duplicate entries are below each other, and then sorted the last column, so that the employee with the main position =Y is on the top. After removing the duplicates I have the list of employees that is required. 

 

The problem now is, Employee 5500 is the head of support and HR. If his position as Head HR which is not his primary employment is removed, the HR department is missing the parent department and the hierarchy is broken, since the parent id does not exist in the list of department ids anymore. Same with Employee 3300 who is head of finance and also head of corporate finance. 

Is there a way or a rule to implement that if the parent department does not exist, move them up one level?

In this case HR with Department ID 311,312 & 313 would have now 300 as their parent department and Tax would have 300 as his parent department. 

Really appreaciate Your support! 

The report is accessible in this Gdrive 

 

[jennratten]

Can you please show what your employee data looks like before duplicates are removed or other changes are made?  How are the assigments of Dept 01, 02, 03, etc created?  Is there a reason why you need to have the department hierarchy and employee data on the same Power Query table as opposed to having two table that are related in the data model?

[patkang]

@jennratten Thanks for your message. The file that i am working with already combined the information from two tables into one. As the department table is associated via the employee id, and I would have to remove the employee id duplicate, it would lead to the same problem. To see the original data, please refer to the file which I have uploaded in my personal Drive, the underlying data is also there. Appreaciate your support!

[edhans]

You will need to supply the underlying data @patkang 

[patkang]

@edhans Just uploaded the excel file into the same drive folder. Thank you for looking into it!