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. Product Level Component A 1 Part_1 A 1 Asm...
  • ronrsnfld's avatar
    3 years ago
    • 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"