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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
patkang
Helper I
Helper I

Parent Child Hierarchy Problem

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. 

Parent_Child.png

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

Dep Structure.png

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. 

Cleaned table.png

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 

 

4 REPLIES 4
jennratten
Super User
Super User

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?

@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
Super User
Super User

You will need to supply the underlying data @patkang 

edhans_0-1624643739752.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors