Using Power query for appending data from table to customized output

[RajaR]

Dears,
Currently I've an excel spreadsheet (Table1) of BOM's related to multiple products - which is downloaded from SAP.
I've a customized requirement to have the final BOM of all products with only the child parts (child part qty's multiplied based on parent code's qty).

I've uploaded a very shorter version consisting of only 3levels for 2products (1SSR007100 & 1SSR007101). In actual scenario, we've multiple levels with multiple products.
I need output as per 2nd snapshot attached (from columns 'B' to 'F' : wherein I've mentioned remarks in columns 'G & H').

Your reply is greatly helpful, which will reduce our non-value activity currently we are doing.
Awaiting your favorable reply.

Kalesh 2.xlsx
	B	C	D	E	F	G
2	BOM Level	Final FG	Description	BOM component	BOM Component description	Quantity
3	1	1SSR007100	High performance product A	RE00100	Roll 3.2	1
4	1	1SSR007100	High performance product A	RU00801	Basic Product 3ph	1
5	1	1SSR007100	High performance product A	RE00200	Acutator 3.2	1
6	1	1SSR007100	High performance product A	RU00802	Trip coil	2
7	1	1SSR007100	High performance product A	RE00250	Pin 2.5	1
8	2	RU00801	Basic Product 3ph	SE00100	Plate	2
9	2	RU00801	Basic Product 3ph	SU00801	Pole assy 3ph	3
10	2	RU00801	Basic Product 3ph	SE00300	Alloy tip	1
11	2	RU00801	Basic Product 3ph	SE00400	Soldering paste	1
12	2	RU00802	Trip coil	SE00500	Wire core	2
13	2	RU00802	Trip coil	SE00550	Winding frame	2
14	3	SU00801	Pole assy 3ph	TE00150	Terminal 3ph	1
15	3	SU00801	Pole assy 3ph	TE00220	Finger	5
16	1	1SSR007101	High performance product B	RE00100	Roll 3.2	1
17	1	1SSR007101	High performance product B	RU00804	Basic Product 4ph	1
18	1	1SSR007101	High performance product B	RE00255	Acutator 4.5	1
19	1	1SSR007101	High performance product B	RU00802	Trip coil	2
20	1	1SSR007101	High performance product B	RE00250	Pin 2.5	1
21	2	RU00804	Basic Product 4ph	SE00100	Plate	2
22	2	RU00804	Basic Product 4ph	SU00802	Pole assy 4ph	4
23	2	RU00804	Basic Product 4ph	SE00300	Alloy tip	1
24	2	RU00804	Basic Product 4ph	SE00400	Soldering paste	1
25	2	RU00802	Trip coil	SE00500	Wire core	2
26	2	RU00802	Trip coil	SE00550	Winding frame	2
27	3	SU00802	Pole assy 4ph	TE00155	Terminal 4ph	1
28	3	SU00802	Pole assy 4ph	TE00220	Finger	5
Input

Kalesh 2.xlsx
	B	C	D	E	F	G	H
2	Final FG	Description	BOM component	BOM Component description	Quantity
3	1SSR007100	High performance product A	RE00100	Roll 3.2	1
4	1SSR007100	High performance product A	SE00100	Plate	2		RU00801 - Basic Product 3ph
5	1SSR007100	High performance product A	TE00150	Terminal 3ph	3	SU00801 - Pole assy 3ph
6	1SSR007100	High performance product A	TE00220	Finger	15
7	1SSR007100	High performance product A	SE00300	Alloy tip	1
8	1SSR007100	High performance product A	SE00400	Soldering paste	1
9	1SSR007100	High performance product A	RE00200	Acutator 3.2	1
10	1SSR007100	High performance product A	SE00500	Wire core	4	RU00802 - Trip coil
11	1SSR007100	High performance product A	SE00550	Winding frame	4
12	1SSR007100	High performance product A	RE00250	Pin 2.5	1
13	1SSR007101	High performance product B	RE00100	Roll 3.2	1
14	1SSR007101	High performance product B	SE00100	Plate	2		RU00804 - Basic Product 4ph
15	1SSR007101	High performance product B	TE00155	Terminal 4ph	4	SU00802 - Pole assy 4ph
16	1SSR007101	High performance product B	TE00220	Finger	20
17	1SSR007101	High performance product B	SE00300	Alloy tip	1
18	1SSR007101	High performance product B	SE00400	Soldering paste	1
19	1SSR007101	High performance product B	RE00255	Acutator 4.5	1
20	1SSR007101	High performance product B	SE00500	Wire core	4	RU00802 - Trip coil
21	1SSR007101	High performance product B	SE00550	Winding frame	4
22	1SSR007101	High performance product B	RE00250	Pin 2.5	1
Output

Thanks.
Regards,
Chozhan

 

[RajaR]

Dear Team,
Can anyone help...it shall be truly helpful.

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Final FG"}, {{"Count", each _, type table [BOM Level=number, Final FG=text, Description=text, BOM component=text, BOM Component description=text, Quantity=number]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Description", "BOM component", "BOM Component description", "Quantity"}, {"Count.Description", "Count.BOM component", "Count.BOM Component description", "Count.Quantity"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count",{{"Final FG", Order.Ascending}})
in
    #"Sorted Rows"