Calculation of children in list of parents/children

[bartvana]

Hello,

I have a Bill Of Material (BOM) list, where the main item (parent) has several subitems, which in term may or may not have subitems again.

It looks like this:

Parent	Child	Qty of child in parent	Child has children
A	A1	1	0
A	A2	5	1
A2	A2a	2	0
A2	A2b	1	0
B	B1	2	1
B	B2	1	0
B1	B1a	1	0

and so on.

So the item "A" is made of 2 subcomponents, 1 x "A1" and 5 x "A2". 

In its turn, "A2" is composed of 2 x "A2a" and 1 x "A2b".

Or otherwise represented:

• A
  • 1 x A1
  • 5 x A2
    • 2 x A2a
    • 1 x A2b

So A is finally composed of 1 x A1, 10 x A2a and 5 x A2b. It's this final level I'm interested in.

Same for B, etc.

I have also a second table, which is a list of quantities of the main items (parents that are not a child). 

Item	Qty
A	200
B	500

What I need, is a calculation of the quantities of the "last level children" (so children that have no children themselves).

So in this case I would have:

Subcomponent	Quantity
A1	200
A2a	10
A2b	5
B1	1000

etc.

I am familiar with Power Queries (using mainly the UI) and formulas. No VBA please (if possible).

How would you go about this? (Taking into account that subcomponents may appear in different main items, so A2b can appear in C, F, G2, I3c, etc., each in different quantities).

Many thanks in advance!

[#2 · Accepted solution]

@bartvana See if the attached file helps you find a solution for your real BOM data. Not sure, though, about the arithmetic you applied in your last table. This is what I get.

I have only used standard PQ UI commands. Nothing fancy.

[#3]

Great, thanks!
I started experimenting myself in the mean time, and ended up using a similar merge as you. I didn't think about pivoting and unpivoting however, so that was interesting to see. Seems like a perfect solution to me.