BOM Hierarchy with multi level parent and child

[Tring01]

Hi,

I have a data like below I need to create a hierarchy on matrix visual i will explain the scenario like how to show the data on hierarchy. Below is the screenshot for sample data.

I have a 2 columns here 1. parent and 2. child,

I have implemented simple table in page 1 by using child column and I passed the child value to drill through feature for the 2nd page here the hierarchy will start. If i pass child value as 'B' and go to the drill through page the result is like below.

Level1: Here I need to create a custom column and that will do the functionality like this, what ever the value we are getting on the results page that is 'B' will go and filter the parent column and what ever the child values it is showing those details will return in custom column like below screenshot. 

The new column showing the values like "F,E,D,C" in the form of rows. then this column I used in matrix visual in rows field the data should show like this. Initially it looks "+B" if I click on this it will expand like this

-B

    F

    E

    D

    C

The same way I need to create the level 2 custom column, the entire process will be the same for here it is taking the value from level1 column instead of child column. I will explain the scenaro below. if I use the level 2 custom column in the matrix visual in the rows field. the data is showing like this.

-B

    +F

    +E

    +D

    +C

If I select '+D' then the next hierarchy will be like 

-D

     H

     G

     F

is this requirement we can handle it from the power BI or not I was bit concern about it. if someone could help me on this if this is feasible please share the report with some dummy data so that I can replicate the same in my data model.

Thanks in advance for your inputs on this.

[Anonymous · Accepted solution]

Thank you lbendlin

Hi, @Tring01 

Based on your description, I created the following example data:

First, use the merge function in Power Query:

Expand the child column:

The results are as follows:

I've uploaded the PBIX file used in this instance below.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Have a read here:  Parent-Child Hierarchies with multiple parents in Power BI with Power Query (thebiccountant.com)

[Tring01]

Hi, Thanks For the quick reply

I just gone through the provided url but unfortunately that is not suitable for my requirement.

Thanks