SOLVED

Calculation of children in list of parents/children

%3CLINGO-SUB%20id%3D%22lingo-sub-2486973%22%20slang%3D%22en-US%22%3ECalculation%20of%20children%20in%20list%20of%20parents%2Fchildren%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2486973%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Bill%20Of%20Material%20(BOM)%20list%2C%20where%20the%20main%20item%20(parent)%20has%20several%20subitems%2C%20which%20in%20term%20may%20or%20may%20not%20have%20subitems%20again.%3C%2FP%3E%3CP%3EIt%20looks%20like%20this%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EParent%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EChild%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EQty%20of%20child%20in%20parent%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EChild%20has%20children%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EA2a%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EA2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EA2b%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EB1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EB2%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3EB1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3EB1a%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CP%3E0%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eand%20so%20on.%3C%2FP%3E%3CP%3ESo%20the%20item%20%22A%22%20is%20made%20of%202%20subcomponents%2C%201%20x%20%22A1%22%20and%205%20x%20%22A2%22.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20its%20turn%2C%20%22A2%22%20is%20composed%20of%202%20x%20%22A2a%22%20and%201%20x%20%22A2b%22.%3C%2FP%3E%3CP%3EOr%20otherwise%20represented%3A%3C%2FP%3E%3CUL%3E%3CLI%3EA%3CUL%3E%3CLI%3E1%20x%20A1%3C%2FLI%3E%3CLI%3E5%20x%20A2%3CUL%3E%3CLI%3E2%20x%20A2a%3C%2FLI%3E%3CLI%3E1%20x%20A2b%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3ESo%20A%20is%20finally%20composed%20of%201%20x%20A1%2C%2010%20x%20A2a%20and%205%20x%20A2b.%20It's%20this%20final%20level%20I'm%20interested%20in.%3C%2FP%3E%3CP%3ESame%20for%20B%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20also%20a%20second%20table%2C%20which%20is%20a%20list%20of%20quantities%20of%20the%20main%20items%20(parents%20that%20are%20not%20a%20child).%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EItem%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EQty%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E200%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E500%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%2C%20is%20a%20calculation%20of%20the%20quantities%20of%20the%20%22last%20level%20children%22%20(so%20children%20that%20have%20no%20children%20themselves).%3C%2FP%3E%3CP%3ESo%20in%20this%20case%20I%20would%20have%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3ESubcomponent%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3EQuantity%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EA1%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E200%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EA2a%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EA2b%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3EB1%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E1000%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eetc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20familiar%20with%20Power%20Queries%20(using%20mainly%20the%20UI)%20and%20formulas.%20No%20VBA%20please%20(if%20possible).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20you%20go%20about%20this%3F%20(Taking%20into%20account%20that%20subcomponents%20may%20appear%20in%20different%20main%20items%2C%20so%20A2b%20can%20appear%20in%20C%2C%20F%2C%20G2%2C%20I3c%2C%20etc.%2C%20each%20in%20different%20quantities).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2486973%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2487524%22%20slang%3D%22en-US%22%3ERe%3A%20Calculation%20of%20children%20in%20list%20of%20parents%2Fchildren%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2487524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3BSee%20if%20the%20attached%20file%20helps%20you%20find%20a%20solution%20for%20your%20real%20BOM%20data.%20Not%20sure%2C%20though%2C%20about%20the%20arithmetic%20you%20applied%20in%20your%20last%20table.%20This%20is%20what%20I%20get.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-26%20at%2008.14.12.png%22%20style%3D%22width%3A%20209px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291569i334E73942666EF5E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-26%20at%2008.14.12.png%22%20alt%3D%22Screenshot%202021-06-26%20at%2008.14.12.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20only%20used%20standard%20PQ%20UI%20commands.%20Nothing%20fancy.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2488572%22%20slang%3D%22en-US%22%3ERe%3A%20Calculation%20of%20children%20in%20list%20of%20parents%2Fchildren%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2488572%22%20slang%3D%22en-US%22%3EGreat%2C%20thanks!%3CBR%20%2F%3EI%20started%20experimenting%20myself%20in%20the%20mean%20time%2C%20and%20ended%20up%20using%20a%20similar%20merge%20as%20you.%20I%20didn't%20think%20about%20pivoting%20and%20unpivoting%20however%2C%20so%20that%20was%20interesting%20to%20see.%20Seems%20like%20a%20perfect%20solution%20to%20me.%3C%2FLINGO-BODY%3E
Contributor

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:

ParentChildQty of child in parentChild has children
AA110
AA251
A2A2a20
A2A2b1

0

BB12

1

BB21

0

B1B1a1

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

ItemQty
A200
B500

 

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:

SubcomponentQuantity
A1200
A2a10
A2b5
B11000

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

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

Screenshot 2021-06-26 at 08.14.12.png

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

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.