Forum Discussion

MNLegoman's avatar
MNLegoman
Frequent Visitor
3 years ago
Solved

Adding new rows at specific locations

 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.

ProductLevelComponent
A1Part_1
A1Asm_1
A2Part_2
A2Asm_2
A3Part_5
A3Part_123
B1Part_22
B1Asm_23
B2Part_987
B2Part_988
B2Part_989
B2Part_990
C1Part_80
C1Asm_1
C2Part_2
C2Asm_2
C3Part_5
C1Part_990

 

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

ProductLevelComponent
A0A
A1Part_1
A1Asm_1
A2Part_2
A2Asm_2
A3Part_5
A3Part_123
B0B
B1Part_22
B1Asm_23
B2Part_987
B2Part_988
B2Part_989
B2Part_990
C0C
C1Part_80
C1Asm_1
C2Part_2
C2Asm_2
C3Part_5
C1Part_990

 

Any help is appreciated.

    • 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"

     

     

1 Reply

    • 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"