Manager Hierarchy Problem

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:

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:

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