SOLVED

Contributor

# Calculation of children in list of parents/children

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 Replies
best response confirmed by bartvana (Contributor)
Solution

# Re: Calculation of children in list of parents/children

@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.

# Re: Calculation of children in list of parents/children

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.