Forum Discussion
MNLegoman
3 years agoFrequent Visitor
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...
- 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"
ronrsnfld
3 years agoSuper User
- 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"