Parent-child flattening (multiple levels)

[bartvana]

Hello,

I have an interesting problem to work out and I'm sure there's a very efficient and elegant solution using Power Query. Before I start tinkering myself (in probably not the most efficient way): how would you approach this?

I have a dataset with parents, children and quantities of children in the parent. 

I also have sales figures (quantities) of (some of the) parents and children. Some components are sold both as-is, and as part of a parent.

What I need is the quantity of all components, flattened. See below (and attached workbook):

Any help to put me on the right track would be greatly appreciated!

Note that:

• Some of the children contain grandchildren, which in turn contain grand-grandchildren, and so on, and the maximum depth is unknown beforehand.

[#2]

@bartvana 

Eventually you'll have to want to find out if you're making a profit! Since you're doing that now, why not incorporate that aspect now and find out if there will be issues now.

can you imagine creating a system to calculate the quantities then later on you realize you're going to have to change it "again".

[#3]

That's an interesting thought, but in this case it's a question of how much (in quantity) parts we need to order. So I really just need the desired result as stated in the workbook (and the screenshot).
The actual use case is that we have a bunch of finished products with their parts and subparts. Based on the sales quantities of the finished products, I need the quantities of the corresponding parts & subparts.

[#4]

@bartvana 

I try to understand the BOM structure. So I made a BOM explosion.

I noticed that TOYCARSET1 and TOYCARSET2 are the top levels. Which is not in line with the sales table showing also TOYCAR and WHEEL.

Then I noticed that TYRE-OUTERTYRE and TYRE-INNERTYRE are on two levels.

Does that make sense?

[#5]

@bartvana 

That's great, thank for sharing. I'd only suggest to expand on any number of levels. 

First, let create the function which calculates number of subparts in previous level. That is exactly your query defines as fntCalc() function:

( tCalc as table ) =>

let
    Source = tCalc,
    // NEXT IS INITAL QUERY, SKIPPED HERE
    // ...
in
    #"Renamed Columns1"

Next function fntCalcSubParts() iterates level by level and stops iterations if previous level table is identical to one on current level. This is function with recursion:

( tCalc as table ) =>

let
    nextLevel = fntCalc( tCalc )

in
    if  tCalc = nextLevel
    then nextLevel
    else fntCalcSubParts (nextLevel)

Use it to calculate final subparts table

let
    Source = Excel.CurrentWorkbook(){[Name="tBOM"]}[Content],
    nextLevel = fntCalcSubParts( Source )
in
    nextLevel

And use above as source for you tResult query.

All above is in Variant 02 group in attached.

[#6]

@Detlef Lewin , here is not exact hierarchy.

A could be part of B

B could be part of C; and could be part of D;  and could be sold as separate product

A could be part of D not as part of B but directly (in addition to be in B)

etc

[#7]

@bartvana 

Points to Ponder in your Parent Child hierarchy

if you manufacture 500 toy cars

and you can only purchase toy wheels by cases

each case contains 20 boxes of wheels

each box contains 100 wheels

How many cases of wheels do you have to buy in order to fulfill your manufacturing needs for the 500 toy cars.

considering each car only needs 4 wheels each never mind the cost of each wheel but the question is how many cases do you need to order for the 500 toy cars

and it boils down to the lowest unit of measure.

[#8]

@Sergei Baklan This is exciting, we're really getting there! I nailed the result by changing my approach, so that part of the problem (the function in itself) is now solved.

But now it needs to be improved using a conditional loop, somewhat similar to what you proposed, but what I do now is calculate 5 separate tables and append them afterwards, so I don't really know how to implement your function strategy.

Could you give a hand please?

So the result is now exactly what I wanted:

And I do this using one function and the following queries:

• tSales and tBOM are simply the basic data, and tCalc1 fetches the sales quantities for the parents (so it's a bit different from the rest, because it doesn't fetch subparts)
• tCalc2 to tCalc5 are the daisychain of the function. They each result in a table that's fed into the next loop, and at the end I append all these tables. I know I'm done when the last table is empty.
• tSalesRenamed is simply tSales with the columns renamed so I can append it easily to tCalc1-5.
• tResult is tCalc1-5 and tSalesRenamed all appended (and grouped)

So basically, what I need, in plain English, is:

1. Start from the result of tCalc1
2. Execute fnCalc and store the resulting table somewhere
3. Execute fnCalc on the result from the previous step, and store that table also
4. Repeat 3 until the result from fnCalc is empty
5. Append all the stored tables (and do some final manipulations which are no problem)

If we can get this done, we're completely there!

[#9 · Accepted solution]

@bartvana 

I didn't dive a deep into calculations, here is only suggestion for iterations.

We keep as it is now tBOM and tCalc1.

Levels are iterated by fntCalcQty:

( tCalc as table, tBasis as table, tAccumulate as table ) =>

let
    nextLevel = fnCalc( tCalc, tBasis ),
    addTable = Table.Combine( {tAccumulate, nextLevel} )

in
    if  Table.RowCount( nextLevel ) = 0
    then tAccumulate
    else fntCalcQty (nextLevel, tBasis, addTable)

It iterates till first empty table appears.

Final result is calculated as

let
    Source = fntCalcQty( tCalc1, tBOM, tCalc1),
    addSales = Table.Combine( {Source, tSalesRenamed} ), 
    #"Grouped Rows" = Table.Group(
        addSales, {"Subpart"},
        {{"TotalQty", each List.Sum([TotalQty]), type nullable number}}
    ),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Subpart", Order.Ascending}})
in
    #"Sorted Rows"

Above is in Variant 03 group in attached.

[#10]

Super fabulous, thank you, exactly what I needed!! (A deep dive into the math wasn't necessary, it's just the iteration I was wondering about, so you gave the perfect answer).
I'll implement this asap in my real data and see what it gives. In any case, I learnt a lot and the general strategy is the solution to my problem. You've been very helpful and completely made my day :).

[#11]

so how off was your narrative in describing your problem?

[#12]

@bartvana great, glad it helped