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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shettysunith
Frequent Visitor

Org chart without 'parent id', only a field 'manager'

A have org data in excel. A "Manager' column is used to state the hierachy between the employee and his/her manager. There are no parent id or child id fields available. How can i create an org chart based on "Manager' field alone.
I tried various visuals in the market, all of them are based on parent-child id.
Any help !!
1 ACCEPTED SOLUTION

@Adescrit  I followed steps 1,2 & 3, and then created Index_Parent = LOOKUPVALUE(Table[Index_child], Table[Employee_name], Table[Manager_name] )

After this used a visulaization from market place to create the visuals. This worked !! 

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @shettysunith ,

 

You could check the details below:

Parsing Organizational Hierarchy or Chart of Accounts in Power BI with Parent-child Functions in DAX...

And here's a test file you could refer to.

 

 

Best Regards,

Stephen Tao

 

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

shettysunith
Frequent Visitor

@Adescrit Index that we created in step3 should be unique right ? Because in the step 7 we create again index for managers, where as managers also also there in step 3 where they already have an index and hence these index will be different. So when we merge it will not create the proper parent child relation rather some cyclic relationships.

Adescrit
Impactful Individual
Impactful Individual

Hi @shettysunith 

 

In Power Query you could create a Parent id and child id to enable you to use one of the org chart visuals. The steps would be:

  1. Open Power Query.
  2. With the Org Data table selected, go to Add Column > Index Column > From 1.
  3. Rename the index column "Child Id" (you can name it whatever you want but let's go with Child id for simplicity)
  4. In the Org Data table, right click on the Manager field and select Add as New Query.
  5. From the Transform menu select "Remove Duplicates" and then Convert "To Table". Rename the table "Managers".
  6. In the new table (containing manager names) go to Add Column > Index Column > From 1.
  7. Rename this new index column as "Parent Id"
  8. Select the Org Data table. Select Merge queries, and merge the Org Data and the Managers table via the manager name. The join kind can be Left Outer.
  9. Click on the outward facing arrow icon in the top-right of the new column (containing the word "Table" repeatedly)
  10. Expand only the Parent Id column.
  11. Close & Apply power query.
  12. You can now create a relationship between the Managers table and the Org Data table via the "Parent Id" column. 

 

Will this now allow you to create an org chart?

  1.  

Did I answer your question? Mark my post as a solution!
My LinkedIn

@Adescrit  I followed steps 1,2 & 3, and then created Index_Parent = LOOKUPVALUE(Table[Index_child], Table[Employee_name], Table[Manager_name] )

After this used a visulaization from market place to create the visuals. This worked !! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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