Power BI Tutorial: Flatten Parent Child Hierarchy

EDIT: I recorded a detailed video on how to work with Parent Child hierarchies in Power BI: https://businessintelligist.com/?p=2603

I have been working with SAP data source lately. And in SAP dimensions, parent-child hierarchy is very common and there is a need to flatten it in Power BI. A common parent child hierarchy is an employee table. The employee table has a manager column that determines the hierarchy. E.g

We want to flatten the data, so we it looks like below

Here is how to do it.

• Download the data set from here and load it into Power BI Desktop.
• Navigate to Data view in Power BI Desktop and select Employee table.
• From the ribbon select Modeling -> New Column
• In the formula bar enter:
  Path = PATH(Employee[First Name],Employee[Manager]).
  Here you are using PATH function to create a complete path of the hierarchy
• From the ribbon select Modeling -> New Column
• In the formula bar enter:
  Level 1 = PATHITEM(Employee[Path],1).
  Here you are using PATHITEM function which retrieves the value for the particular level in the hierarchy. In our example, we have 4 levels. Let’s add the remaining 3 columns.
  Level 2 = PATHITEM(Employee[Path],2) 
  Level 3 = PATHITEM(Employee[Path],3) 
  Level 4 = PATHITEM(Employee[Path],4)

Now you have a flattened hierarchy !