Creating hierarchical Thingy from Field

[Orange]

I am working with an Employee Directory data set. One field is called [Company_Org_Path]. Each employee record has this one field that shows the Organization Path they are in. The organization path uses the company, division, department, and group acronym. The record set is about 50,000 records.

I presume that Power Bi cannot automatically generate a hierarchy from my [Company_Org_Path] like it does for a date field.

Here is an example subset of an employee record.

EmployeeTable

Name, Title, [Company_Org_Path]

Jack Frost, CEO, [ABC]

Robbie Reindeer, Division Manager, [ABC DM]

Ellen Elflen, Shipping Department Manager, [ABC DM SDDM]

Sandy Claws, Truck Driver, [ABC DM SDDM TD]

etc.

The depth of the Organization Tree varys.

To create a Hierarchy, I believe I have several methods.

Option One, break up the [Company_Org_Path] into additional columns for each employee record. For example,

Name,[Company_Org_Path], [Company], [Division], [Department],[Section],[etc.]

Jack Frost, [ABC], [null], [null], [null], [null], [null]

Sandy Claws, [ABC], [SDM], [SDDM], [TD], [null], [null]

etc.

Option Two, create some kind of table and link to it from the employee table? This would use less storage but not sure if this would hurt or help performance. Not sure how to generate this table from the source table yet, but this is what the resulting table would look like?

OrganizationTable

id,CompanyOrgPath,parent-id

1,ABC,null

2,DM,1

3,SDM,2

4,SDDM,3

5,TD,4

etc.

Am I on the right path here? Not looking for the answer, but a little nudge in the right direction would be nice. I have been searching for guidance for several days now but always seem to come up a little short.

Thanks,

[gckcmc · Accepted solution]

So I work in a company with a sizeable number employees as well.  We too have a hierarchy like what you describe.

I've built my Org hierarchy with the levels each becoming a column.  I would suggest making more columns than you think you need as the reorganizations can suddenly collapse or expand without notice. 🙂

I've built them as you say in option 1, where you have them null'ed if not needed.  The main reason I do this is to do all sorts of other analysis where you get to view information about either the individual, their # of employees under them (directly or indirectly), and then the org analyses, like # divisions per business, # teams per business, etc.  

Then the employee analysis like how many janitors does division 1 need, vs. division 2....etc.

I have a master table of all the organizations in the company, which I use to merge with other queries when I have a subset of information (like an employee ID), and then want to use that for filtering.  Think building, floor, city, state, country, permanent or temporary employee, returning vs. first time employed at the firm, etc.  Lots of uses.

hope this helps.