Adding new rows at specific locations

[MNLegoman]

 I have a BOM table that looks like the following. I need to make the Product column part of the component column by creating a Level 0.

Product	Level	Component
A	1	Part_1
A	1	Asm_1
A	2	Part_2
A	2	Asm_2
A	3	Part_5
A	3	Part_123
B	1	Part_22
B	1	Asm_23
B	2	Part_987
B	2	Part_988
B	2	Part_989
B	2	Part_990
C	1	Part_80
C	1	Asm_1
C	2	Part_2
C	2	Asm_2
C	3	Part_5
C	1	Part_990

The desired output is this:  
Note that the product Must appear as the 1st component

Product	Level	Component
A	0	A
A	1	Part_1
A	1	Asm_1
A	2	Part_2
A	2	Asm_2
A	3	Part_5
A	3	Part_123
B	0	B
B	1	Part_22
B	1	Asm_23
B	2	Part_987
B	2	Part_988
B	2	Part_989
B	2	Part_990
C	0	C
C	1	Part_80
C	1	Asm_1
C	2	Part_2
C	2	Asm_2
C	3	Part_5
C	1	Part_990

Any help is appreciated.

[ronrsnfld · Accepted solution]

• Group by Product
• In the Aggregation, create a Record that has the appropriate zero level entries
• Prepend this record to the orginal records making up the sub-table

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Level", Int64.Type}, {"Component", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {
        {"Add Row", (t)=> 
            {Record.FromList({t[Product]{0},0,t[Product]{0}},{"Product","Level","Component"})} & 
            Table.ToRecords(t)}
        }),

    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product"}),

    #"Expanded Add Row" = Table.ExpandListColumn(#"Removed Columns", "Add Row"),
    #"Expanded Add Row1" = Table.ExpandRecordColumn(#"Expanded Add Row", "Add Row", {"Product", "Level", "Component"})
in
    #"Expanded Add Row1"