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

[shettysunith]

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 !!

[v-stephen-msft]

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]

@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]

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.  

[shettysunith · 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 !!