Power BI Tutorial: Flatten Parent Child Hierarchy

One comment

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 !

1 comments on “Power BI Tutorial: Flatten Parent Child Hierarchy”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s