Manager Hierarchy Problem

[leeleeds123]

Hi there,

I have a data set that shows each employee and the direct manager of that employee, and the manager's manager, similar to the below:

Employee	Line Manager	Line Manager +1	Line Manager + 2
Fred	Mr Smith	Mr Jones	Mr Adams
Tom	Mr Green	Mr Smith	Mr Red
Joe	Mr Brown	Mr White	Mr Davis

Not everyone in each column is all of the same seniority, it just shows the next level manager up for each employee. So for exampe if you worked for the most senior person the CIO for arguement sake, in Line Manager the name of the CIO would appear and subsequent columns "Line Manager +1" and "Line Manager +2" would be blank.

What i am trying to do with this data is to create columns that show the managers for each employee the manager they work for that sits at each level.

For example:

CIO	CIO + 1	CIO + 2	CIO + 3
Bob	Fred	Joe	Sara
Bob	Fred	Joe	Greg
Bob	Fred	Sam	Claire
Bob	Tom	Richard	Kelly
Bob	Tom	Paul	Liz
Bob	Tom	James	Richard

I am hoping i can get to this table by working just using the data that shows who each person's line manager is that i have detailed above. 

[Shravan133]

In Power BI, you can achieve this transformation using Power Query (M language). Here's how to do it:

1. Load Your Data:

   • Load your dataset into Power BI.
   • Go to the "Home" tab and click on "Transform Data" to open Power Query Editor.

2. Prepare Your Data:

   • Ensure your data is structured correctly with columns for Employee, Line Manager, Line Manager +1, and Line Manager +2.

3. Create Custom Columns for Each Level of Management:

   • Add custom columns to show the hierarchical levels of management for each employee.

Here’s how you can do this step-by-step:

Step-by-Step Process

1. Open Power Query Editor:

   • Click on "Transform Data" to open Power Query Editor.

2. Duplicate the Table:

   • Right-click on your table in the Queries pane and select "Duplicate". This will create a copy of your table to work on.

3. Add Custom Columns:

   • Add custom columns for each hierarchical level.

4. CIO Column:

   • Go to the "Add Column" tab.
   • Click on "Custom Column".
   • Name the column "CIO".
   • Enter the name of the CIO directly if it's the same for all, or leave it blank if it varies.

5. CIO +1 Column:

   • Add another custom column named "CIO + 1".
   • Use the following formula to find the manager for each employee:
     if [Line Manager] = null then null else [Line Manager]

6. CIO +2 Column:

   • Add another custom column named "CIO + 2".
   • Use the following formula:
     if [Line Manager +1] = null then null else [Line Manager +1]

7. CIO +3 Column:

   • Add another custom column named "CIO + 3".
   • Use the following formula:
     if [Line Manager +2] = null then null else [Line Manager +2]

Example M Code

Here’s an example of how the M code might look for creating these custom columns:

let Source = YourTable, AddCIO = Table.AddColumn(Source, "CIO", each "Bob"), // Assuming 'Bob' is the CIO AddCIO1 = Table.AddColumn(AddCIO, "CIO + 1", each if [Line Manager] = null then null else [Line Manager]), AddCIO2 = Table.AddColumn(AddCIO1, "CIO + 2", each if [Line Manager +1] = null then null else [Line Manager +1]), AddCIO3 = Table.AddColumn(AddCIO2, "CIO + 3", each if [Line Manager +2] = null then null else [Line Manager +2]) in AddCIO3

1. Close & Apply:
   • After creating the necessary columns, click on "Close & Apply" to load the data back into Power BI.

Final Table Structure

Your final table should look like this:

Employee Line Manager Line Manager +1 Line Manager +2 CIO CIO + 1 CIO + 2 CIO + 3

Fred	Mr Smith	Mr Jones	Mr Adams	Bob	Mr Smith	Mr Jones	Mr Adams
Tom	Mr Green	Mr Smith	Mr Red	Bob	Mr Green	Mr Smith	Mr Red
Joe	Mr Brown	Mr White	Mr Davis	Bob	Mr Brown	Mr White	Mr Davis

This approach assumes the CIO is constant for all employees. If the CIO varies, you'll need to adjust the formula in the "CIO" column accordingly