Appending data based on custom requirement using Power Query formula

[RajaR]

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

All 3images I've uploaded in 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.
Hence while replying kindly share bit of explanation (will help me to understand).

Thanks in advance.
Regards,
Raja

Table1

Table 1 - Base Data
FG#	FG Description	Level1 Code	Level1 Description	Level1 Qty	Level2 code	Level2 Desc
3SAA100001R1	Product 101A	2SAA111110R1	Basic CB	1	3SAR0000001A901	Basic CB 101A
3SAA100001R1	Product 101A	2SAA111111R1	Frontal	1	3SAR0000002A901	Frontal 101A
3SAA100001R1	Product 101A	2SAA111112R1	KIT 2pcs	1	3SAR0000003A901	KIT 2pcs 101A
3SAA100001R1	Product 101A	2SAA111112R1	KIT 2pcs	1	3SAR0000003A901	KIT 2pcs 101A

Table2

Table 2 - Level2 BOM
Level	Level2 BOM	Level2 BOM Desc	Qty
2	3SAR0000001A901	Basic CB 101A	1
3	RP000001	Screw	2
3	3SAR0011001	LONG SCREW	5
3	3SAR0011002	SIGNALLING R	1
4	3SAR0022001	SIGNALLING R1	1
3	3SAR0011003	SIGNALLING L	1
4	2SAR1111101811	SIGNALLING L1	1
5	RU2341	Shaft	1
5	RP9021	Spring	2
5	QP10001810	Lever Assy
6	AA1234	Moving	1
6	AA1235	Screw	1
2	3SAR0000002A901	Frontal 101A	1
3	RA00001801	Label assy	1
4	RY56789	Counter	1
4	RR1234	Plate	1
2	3SAR0000003A901	KIT 2pcs 101A	1
3	XX123	Kit 2A	1
3	YY1234	Cap	1
4	RR3452	Fixture	3

Table3

Table 3 - Required Output
FG#	FG Description	Level	Code	Description	Qty	Remarks
3SAA100001R1	Product 101A	1	2SAA111110R1	Basic CB	1	Level1 Code From C2
3SAA100001R1	Product 101A	2	3SAR0000001A901	Basic CB 101A	1	Level2 Code From F2
3SAA100001R1	Product 101A	3	RP000001	Screw	2	From Level2 BOM table
3SAA100001R1	Product 101A	3	3SAR0011001	LONG SCREW	5	""
3SAA100001R1	Product 101A	3	3SAR0011002	SIGNALLING R	1	""
3SAA100001R1	Product 101A	4	3SAR0022001	SIGNALLING R1	1	""
3SAA100001R1	Product 101A	3	3SAR0011003	SIGNALLING L	1	""
3SAA100001R1	Product 101A	4	2SAR1111101811	SIGNALLING L1	1	""
3SAA100001R1	Product 101A	5	RU2341	Shaft	1	""
3SAA100001R1	Product 101A	5	RP9021	Spring	2	""
3SAA100001R1	Product 101A	5	QP10001810	Lever Assy		""
3SAA100001R1	Product 101A	6	AA1234	Moving	1	""
3SAA100001R1	Product 101A	6	AA1235	Screw	1	""
3SAA100001R1	Product 101A	1	2SAA111111R1	Frontal		Level1 Code From C3
3SAA100001R1	Product 101A	2	3SAR0000002A901	Frontal 101A		Level2 Code From F3
3SAA100001R1	Product 101A	2	3SAR0000002A901	Frontal 101A	1	From Level2 BOM table
3SAA100001R1	Product 101A	3	RA00001801	Label assy	1	""
3SAA100001R1	Product 101A	4	RY56789	Counter	1	""
3SAA100001R1	Product 101A	4	RR1234	Plate	1	""
3SAA100001R1	Product 101A	1	2SAA111112R1	KIT 2pcs	1	Level1 Code From C4
3SAA100001R1	Product 101A	2	3SAR0000003A901	KIT 2pcs 101A	1	Level2 Code From F4
3SAA100001R1	Product 101A	2	3SAR0000003A901	KIT 2pcs 101A	1	From Level2 BOM table
3SAA100001R1	Product 101A	3	XX123	Kit 2A	1	""
3SAA100001R1	Product 101A	3	YY1234	Cap	1	""
3SAA100001R1	Product 101A	4	RR3452	Fixture	3	""
3SAA100001R1	Product 101A	1	2SAA111112R1	KIT 2pcs	1	Level1 Code From C5
3SAA100001R1	Product 101A	2	3SAR0000003A901	KIT 2pcs 101A	1	Level2 Code From F5
3SAA100001R1	Product 101A	2	3SAR0000003A901	KIT 2pcs 101A	1	From Level2 BOM table
3SAA100001R1	Product 101A	3	XX123	Kit 2A	1	""
3SAA100001R1	Product 101A	3	YY1234	Cap	1	""
3SAA100001R1	Product 101A	4	RR3452	Fixture	3	""

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Level2BOM"]}[Content],
    Renamed = Table.RenameColumns(Source,{{"Level2 BOM", "Code"}, {"Level2 BOM Desc", "Description"}}),
    AddL2 = Table.AddColumn(Renamed, "L2", each if [Level] =2 then [Code] else null),
    L2 = Table.FillDown(AddL2,{"L2"}),
    MergedL2 = Table.NestedJoin(Excel.CurrentWorkbook(){[Name="BaseData"]}[Content], {"Level2 code"}, L2, {"L2"}, "Base", JoinKind.LeftOuter),
    Renamed2 = Table.RenameColumns(MergedL2,{{"Level1 Code", "Code"}, {"Level1 Description", "Description"}, {"Level1 Qty", "Qty"}}),
    AddedL1L2 = Table.AddColumn(Renamed2, "Custom", each Table.Combine({Table.AddColumn(Table.FromRecords({_}),"Level", each 1),[Base]})  ),
    RemovedColumns = Table.RemoveColumns(AddedL1L2,{"Code", "Description", "Qty", "Level2 code", "Level2 Desc", "Base"}),
    ExpandedCustom = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Level","Code", "Description", "Qty"})
in
    ExpandedCustom

FG#	FG Description	Level	Code	Description	Qty
3SAA100001R1	Product 101A	1	2SAA111110R1	Basic CB	1
3SAA100001R1	Product 101A	2	3SAR0000001A901	Basic CB 101A	1
3SAA100001R1	Product 101A	3	RP000001	Screw	2
3SAA100001R1	Product 101A	3	3SAR0011001	LONG SCREW	5
3SAA100001R1	Product 101A	3	3SAR0011002	SIGNALLING R	1
3SAA100001R1	Product 101A	4	3SAR0022001	SIGNALLING R1	1
3SAA100001R1	Product 101A	3	3SAR0011003	SIGNALLING L	1
3SAA100001R1	Product 101A	4	2SAR1111101811	SIGNALLING L1	1
3SAA100001R1	Product 101A	5	RU2341	Shaft	1
3SAA100001R1	Product 101A	5	RP9021	Spring	2
3SAA100001R1	Product 101A	5	QP10001810	Lever Assy
3SAA100001R1	Product 101A	6	AA1234	Moving	1
3SAA100001R1	Product 101A	6	AA1235	Screw	1
3SAA100001R1	Product 101A	1	2SAA111111R1	Frontal	1
3SAA100001R1	Product 101A	2	3SAR0000002A901	Frontal 101A	1
3SAA100001R1	Product 101A	3	RA00001801	Label assy	1
3SAA100001R1	Product 101A	4	RY56789	Counter	1
3SAA100001R1	Product 101A	4	RR1234	Plate	1
3SAA100001R1	Product 101A	1	2SAA111112R1	KIT 2pcs	1
3SAA100001R1	Product 101A	2	3SAR0000003A901	KIT 2pcs 101A	1
3SAA100001R1	Product 101A	3	XX123	Kit 2A	1
3SAA100001R1	Product 101A	3	YY1234	Cap	1
3SAA100001R1	Product 101A	4	RR3452	Fixture	3
3SAA100001R1	Product 101A	1	2SAA111112R1	KIT 2pcs	1
3SAA100001R1	Product 101A	2	3SAR0000003A901	KIT 2pcs 101A	1
3SAA100001R1	Product 101A	3	XX123	Kit 2A	1
3SAA100001R1	Product 101A	3	YY1234	Cap	1
3SAA100001R1	Product 101A	4	RR3452	Fixture	3

 

[RajaR]

That's awesome!!!.... ? Thanks a lot....
I will try for bulk data & revert for any issues.....

Once again thanks guy's......