Forum Discussion
Manager Hierarchy Problem
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.
1 Reply
In Power BI, you can achieve this transformation using Power Query (M language). Here's how to do it:
Load Your Data:
- Load your dataset into Power BI.
- Go to the "Home" tab and click on "Transform Data" to open Power Query Editor.
Prepare Your Data:
- Ensure your data is structured correctly with columns for Employee, Line Manager, Line Manager +1, and Line Manager +2.
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
Open Power Query Editor:
- Click on "Transform Data" to open Power Query Editor.
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.
Add Custom Columns:
- Add custom columns for each hierarchical level.
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.
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]
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]
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- 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 + 3Fred 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
