Forum Discussion
Solved
Flat BOM with Multiple Parents Components
Hello all, I do hope some of you can help me because I think this is a tricky request. I have two charts: 1. This is the Main table, that represents the father product 2. In this p...
1. Yes it is. Merely a matter of changing how you expand the Joined table:
let Source = Excel.CurrentWorkbook(){[Name="BillOfMaterial"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Integrated_Check_Valve", type text}, {"Connection_Type_Front_Port", type text}, {"Valve_Function", type text}, {"Voltage", type text}, {"Sealing_Material", type text}, {"Power_Consumption", type text}, {"Pipe_Size_Front_Port", type text}, {"Pipe_Size_Side_Port", type text}, {"Orifice_Size", type text}, {"Core SubAssy", type text}}), #"Add Blank Rows" = List.Accumulate( {1..Table.RowCount(#"Changed Type")-1}, {}, (s,c)=> s & {#"Changed Type"{c}, Record.FromList( List.Repeat({null},Table.ColumnCount(#"Changed Type")), Table.ColumnNames(#"Changed Type"))}), #"To Table" = Table.FromRecords(#"Add Blank Rows", type table [Integrated_Check_Valve=text,Connection_Type_Front_Port= text, Valve_Function=text, Voltage=text, Sealing_Material=text, Power_Consumption=text, Pipe_Size_Front_Port=text, Pipe_Size_Side_Port=text, Orifice_Size=text, Core SubAssy=text]), #"Join Core SubAssy" = Table.NestedJoin(#"To Table","Core SubAssy", Core_SubAssy,"Part Number", "Part Numbers", JoinKind.LeftOuter), #"Move Join Down" = Table.FromColumns( Table.ToColumns(#"To Table") & {{#table({},{})} & List.RemoveLastN(#"Join Core SubAssy"[Part Numbers],1)},Table.ColumnNames(#"Join Core SubAssy")), #"Extract Part Nums" = Table.TransformColumns(#"Move Join Down", {"Part Numbers",each try List.Transform(List.Skip(Record.FieldValues(Table.ToRecords(_){0}),3), each Text.From(_)) otherwise null, type {text}}), #"Expanded Join" = Table.ExpandListColumn(#"Extract Part Nums", "Part Numbers") in #"Expanded Join"To get:
2. Yes you may.
3. You may also mark one or both answers as accepted, if they meet your requirements.
It works, many thanks.
Two questions:
1. Is it possible to have this structure instead:
2. I need to study your code, May I ask you some question during my analysis?
Thanks again.
1. Yes it is. Merely a matter of changing how you expand the Joined table:
let
Source = Excel.CurrentWorkbook(){[Name="BillOfMaterial"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Integrated_Check_Valve", type text}, {"Connection_Type_Front_Port", type text},
{"Valve_Function", type text}, {"Voltage", type text}, {"Sealing_Material", type text},
{"Power_Consumption", type text}, {"Pipe_Size_Front_Port", type text},
{"Pipe_Size_Side_Port", type text}, {"Orifice_Size", type text}, {"Core SubAssy", type text}}),
#"Add Blank Rows" = List.Accumulate(
{1..Table.RowCount(#"Changed Type")-1},
{},
(s,c)=> s & {#"Changed Type"{c},
Record.FromList(
List.Repeat({null},Table.ColumnCount(#"Changed Type")),
Table.ColumnNames(#"Changed Type"))}),
#"To Table" = Table.FromRecords(#"Add Blank Rows", type table [Integrated_Check_Valve=text,Connection_Type_Front_Port= text,
Valve_Function=text, Voltage=text, Sealing_Material=text,
Power_Consumption=text, Pipe_Size_Front_Port=text,
Pipe_Size_Side_Port=text, Orifice_Size=text, Core SubAssy=text]),
#"Join Core SubAssy" = Table.NestedJoin(#"To Table","Core SubAssy", Core_SubAssy,"Part Number", "Part Numbers", JoinKind.LeftOuter),
#"Move Join Down" = Table.FromColumns(
Table.ToColumns(#"To Table") &
{{#table({},{})} & List.RemoveLastN(#"Join Core SubAssy"[Part Numbers],1)},Table.ColumnNames(#"Join Core SubAssy")),
#"Extract Part Nums" = Table.TransformColumns(#"Move Join Down",
{"Part Numbers",each
try List.Transform(List.Skip(Record.FieldValues(Table.ToRecords(_){0}),3), each Text.From(_)) otherwise null, type {text}}),
#"Expanded Join" = Table.ExpandListColumn(#"Extract Part Nums", "Part Numbers")
in
#"Expanded Join"
To get:
2. Yes you may.
3. You may also mark one or both answers as accepted, if they meet your requirements.