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_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.
- 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
- ronrsnfldSuper 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"