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.
Hello
for sure I will accept your solution because it is exactly what I asked for.
MANY THANKS!!
As I dont have only one subgroup, but I can have more than 1, I was trying to make a custom function:
(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[KeyColumn_SubGroup_Table],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}})
in
Extract_Part_Nums
Everything if fine up to the line : Merge_SubGroup
The rest is not working. I think the problem is here:
List.RemoveLastN(Merge_SubGroup[KeyColumn_SubGroup_Table],1)
May you help?
The relevant column name is hard-coded in the step above within the query, not an argument.
So proper syntax would be:
List.RemoveLastN(Merge_SubGroup[Part Numbers],1)},
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_Joinand 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")?