Forum Discussion
Flat BOM with Multiple Parents Components
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.
So the complete code is:
(Starting_Table, SubGroup_Table, KeyColumn_Starting_Table as text, KeyColumn_SubGroup_Table as text) =>
let
Header_Startin_Table = Table.ColumnNames (Starting_Table),
Starting_TableToText = Table.TransformColumnTypes (
Starting_Table,
List.Transform (
Header_Startin_Table,
each {_, type text}
)
),
Add_Blank_Rows = List.Accumulate(
{1..Table.RowCount(Starting_TableToText)-1},
{},
(s,c)=> s & {Starting_TableToText{c},
Record.FromList(
List.Repeat({null},Table.ColumnCount(Starting_TableToText)),
Table.ColumnNames(Starting_TableToText))}),
To_Table = Table.FromRecords(
Add_Blank_Rows,
Header_Startin_Table),
Merge_SubGroup = Table.NestedJoin(To_Table,KeyColumn_Starting_Table, SubGroup_Table, KeyColumn_SubGroup_Table, "Part Numbers", JoinKind.LeftOuter),
Move_Join_Down = Table.FromColumns(
Table.ToColumns(To_Table) &
{{#table({},{})} & List.RemoveLastN(Merge_SubGroup[Part Numbers],1)},Table.ColumnNames(Merge_SubGroup)),
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
and I invoked in this way:
Flat_CoreSubAssy = Flat_BOM(#"Expanded CoreTube_SubAssy", Core_SubAssy, "Core SubAssy Part Number", "Core SubAssy Part Number"),
Flat_Coil = Flat_BOM(Flat_CoreSubAssy, Coil,"Coil Part Number","Coil Part Number")
and I got this error:
An error occurred in the ‘’ query. Expression.Error: The column 'Part Numbers' already exists in the table
Your function seems to work OK and outputs the desired table given what I think are proper inputs. So the problem would seem to be in your data set or your calling query. The error message suggests a naming conflict.
Yes exactly, each time I call the function, the column is always named "Part Numbers". Is it possible, in your opinion to link the name "Part Numbers" to the father column name (e.g. "Core Tube SubAssy Part Number")?
With no representative data and desired output from that data, I can't really tell what you are doing. But, if you are creating multiple tables from a given data set, you might want to look into combining the tables created by your function, instead of merging the created table. You could use LIst.Accumulate for the combining.