Forum Discussion

iBusinessBI's avatar
iBusinessBI
Icon for Kudo Collector rankKudo Collector
3 years ago
Solved

Power Query - flatten Parent-child hierarchy

I have a parent-child hierarchy table. I don't know in advance how many levels there are.

I need help with building a function that would flatten the levels.

I found this very helpful post:

https://pivotalbi.com/dynamically-flatten-a-parent-child-hierarchy-using-power-query-m/

It does everything. But it leaves "low" levels empty if the hierarchy is not longest.

So I basically ask for your help altering some M code to fill the empty values with the values from level above.

Here is a Power BI file

https://1drv.ms/u/s!AoP_9ampPIT7gdFtb4M-04m-1Z6JgQ?e=9EIk25

Thanks

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    You are not just flattening the hierarchy - you are also forcibly converting it from ragged to fixed depth.  Doing that with repetitions of values is generally frowned upon. While the function you mentioned could be modified that would include a second run to identify the maximum level of depth before you would then traverse the hierarchy.  I don't think it is a good idea.  What's the next step?  

     

    Note that Power Query producing variable output is asking for trouble.  Your downstream processes (Power BI Meta data) will break.

  • ChandanaMB13's avatar
    ChandanaMB13
    Frequent Visitor
    2 years ago

    Hey how do you integrate/ define relationship between actual table data and this data in the bi visuals. 

    I had a data table which had parentid and childid, I used this and created the levels and added it to slicer now I want my visual to show me data related to only the selected childid or group id.Any idea on this