Appending data based on reference from multiple tables using Power Query

[RajaR]

Dears,
I've a custom requirement for product Bill of material (BOM) creation. I've two tables of data (Table1 - Level1 BOM & Table2 - Level2 BOM).
I need output as per Table3.

Below I've uploaded images of the 3 tables, using XL2BB add-in.

Also I've very little knowledge in Power query. Attached are sample files, wherein in actual I've to work with >20k rows of data for both Table1 & 2.
It shall be truly helpful, if you can share the source code (to be used in Power query) for this requirement.

Thanks in advance.
Regards,

Raja

WC BOM_Test Book1.xlsx
	A	B	C	D	E
1	Table 1 - Level1 BOM
2	FG Code	FG Description	Level1 Code	Level1 Description	Level1 Qty
3	3SAA100001R1	Product 101A	AR9300807	Basic CB	2
4	3SAA100001R1	Product 101A	BR8220001	Plate	1
5	3SAA100001R1	Product 101A	BR8222910	KIT 2pcs	1
6	3SAA100001R1	Product 101A	AR9300810	Frontal	2
7	3SAA222222R1	Product 222A	AR9300901	Basic Breaker	1
8	3SAA222222R1	Product 222A	AR9300910	Facia	2
9	3SAA222222R1	Product 222A	BR8220005	Cover	1
10	3SAA222222R1	Product 222A	BR8222805	Terminal	3
Master BOM

WC BOM_Test Book1.xlsx
	A	B	C	D	E
12	Table 2 - Level2 BOM
13	Level1 Code	Level1 Description	Level2 Code	Level2 Description	Level2 Qty
14	AR9300807	Basic CB	1SDR00001	CT	3
15	AR9300807	Basic CB	1SDR00002	Wiring	2
16	AR9300807	Basic CB	1SDR00003	Tube	1
17	BR8220001	Plate	1SDR00004	Nut	1
18	BR8222910	KIT 2pcs	YSDR00001	Cover	1
19	BR8222910	KIT 2pcs	YSDR00002	Washer	2
20	BR8222910	KIT 2pcs	YSDR00003	Screw	2
21	AR9300810	Frontal	ZSDR00001	ROD	2
22	AR9300810	Frontal	ZSDR00002	Tie	2
23	AR9300901	Basic Breaker	PSDR00001	CT 8	8
24	AR9300901	Basic Breaker	PSDR00002	RP	1
25	AR9300901	Basic Breaker	PSDR00003	TU	1
26	AR9300910	Facia	RSDR00001	Label	2
27	AR9300910	Facia	RSDR00002	Sleeve	1
28	BR8222805	Terminal	PPSR00001	Washer 1	3
29	BR8222805	Terminal	PPSR00002	Bolt	3
Master BOM

WC BOM_Test Book1.xlsx
	A	B	C	D	E	F
32	Table 3 - Required Output
33	FG Code	FG Description	BOM Code	BOM Description	Qty	Remarks
34	3SAA100001R1	Product 101A	AR9300807	Basic CB	2	From Table1 - Level1 Code, Description, Qty
35	3SAA100001R1	Product 101A	1SDR00001	CT	6	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
36	3SAA100001R1	Product 101A	1SDR00002	Wiring	4	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
37	3SAA100001R1	Product 101A	1SDR00003	Tube	2	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
38	3SAA100001R1	Product 101A	BR8220001	Plate	1	From Table1 - Level1 Code, Description, Qty Also, this has no level2 BOM details….
39	3SAA100001R1	Product 101A	BR8222910	KIT 2pcs	1	From Table1 - Level1 Code, Description, Qty
40	3SAA100001R1	Product 101A	YSDR00001	Cover	1	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
41	3SAA100001R1	Product 101A	YSDR00002	Washer	2	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
42	3SAA100001R1	Product 101A	YSDR00003	Screw	2	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
43	3SAA100001R1	Product 101A	AR9300810	Frontal	2	From Table1 - Level1 Code, Description, Qty
44	3SAA100001R1	Product 101A	ZSDR00001	ROD	2	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
45	3SAA100001R1	Product 101A	ZSDR00002	Tie	2	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
46	3SAA222222R1	Product 222A	AR9300901	Basic Breaker	1	From Table1 - Level1 Code, Description, Qty
47	3SAA222222R1	Product 222A	PSDR00001	CT 8	8	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
48	3SAA222222R1	Product 222A	PSDR00002	RP	1	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
49	3SAA222222R1	Product 222A	PSDR00003	TU	1	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
50	3SAA222222R1	Product 222A	AR9300910	Facia	2	From Table1 - Level1 Code, Description, Qty
51	3SAA222222R1	Product 222A	RSDR00001	Label	4	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
52	3SAA222222R1	Product 222A	RSDR00002	Sleeve	2	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
53	3SAA222222R1	Product 222A	BR8220005	Cover	1	From Table1 - Level1 Code, Description, Qty Also, this has no level2 BOM details….
54	3SAA222222R1	Product 222A	BR8222805	Terminal	3	From Table1 - Level1 Code, Description, Qty
55	3SAA222222R1	Product 222A	PPSR00001	Washer 1	9	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
56	3SAA222222R1	Product 222A	PPSR00002	Bolt	9	From Table2 - Level2 Code, Description, (Level2 Qty * Level1 Qty)
Master BOM

Cell Formulas
Range	Formula
C34:E34	C34	=C3
C35:D37	C35	=C14
E35:E37	E35	=E14*$E$3
C38:E39	C38	=C4
C40:D42	C40	=C18
E40:E42	E40	=E18*$E$5
C43:E43	C43	=C6
C44:E45	C44	=C21
C46:E46	C46	=C7
C47:D49	C47	=C23
E47:E49	E47	=E23*$E$7
C50:E50	C50	=C8
C51:D52	C51	=C26
E51:E52	E51	=E26*$E$8
C53:E54	C53	=C9
C55:D56	C55	=C28
E55:E56	E55	=E28*$E$10

 

[RajaR]

Dears,
Can anyone help me on this requirement - using Power query??
Thanks.

Regards,
Raja

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Renamed = Table.RenameColumns(Source,{{"Level1 Code", "BOM Code"}, {"Level1 Description", "BOM Description"}, {"Level1 Qty", "Qty"}}),
    Join = Table.NestedJoin(Renamed, {"BOM Code"}, Table.RenameColumns(Excel.CurrentWorkbook(){[Name="Table2"]}[Content],{{"Level2 Code", "BOM Code"}, {"Level2 Description", "BOM Description"}, {"Level2 Qty", "Qty"}}), {"Level1 Code"}, "L2", JoinKind.LeftOuter),
    AddBOM = Table.AddColumn(Join, "BOM", each let 
        a =Table.TransformColumns(Table.SelectColumns([L2], List.Skip(Table.ColumnNames([L2]),2)),{"Qty", (x)=> x * [Qty] }),
        b = Table.InsertRows(a,0, {Record.SelectFields(_, List.Range(Record.FieldNames(_),2,3))} )
        in b),
    RemovedColumns = Table.RemoveColumns(AddBOM,{"BOM Description", "Qty", "L2"}),
    ExpandedBOM = Table.ExpandTableColumn(RemovedColumns, "BOM", {"BOM Code", "BOM Description", "Qty"}, {"BOM Code.1", "BOM Description", "Qty"})
in
    ExpandedBOM

 

[RajaR]

Dear Bo_Ry,
Thanks a lot for your quick response & support.... It's working fabulously.
I will check for mass data & revert in case of any query.

Once again, thanks.

Regards,
Raja