Flat BOM with Multiple Parents Components

[Mic1979]

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 product I have the sub group as follow:

What I would like to have is, for each row of the father, to have rows and columns added based on the number of the components making the subgroup.

Is it possible with Power Query?

I found some resources on the web, but nothing satisfying my request.

Thanks.

[Mic1979]

I thought in this way:

Merge_SubGroup = Table.NestedJoin(To_Table,KeyColumn_Starting_Table, SubGroup_Table, KeyColumn_SubGroup_Table, "Parts for "&KeyColumn_Starting_Table, JoinKind.LeftOuter),

Result:

MyMergedHeader = List.Last (Table.ColumnNames(Merge_SubGroup)),

Result: 

Move_Join_Down = Table.FromColumns(
Table.ToColumns(To_Table) &
{{#table({},{})} & List.RemoveLastN(Merge_SubGroup[MyMergedHeader],1)},Table.ColumnNames(Merge_SubGroup)),

But I still get this error:

[ronrsnfld]

See my previous response.

For more focused assistance, you will need to provide a usable data set and the code which reproduces the error.

[Mic1979]

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?

[ronrsnfld]

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)},

[Mic1979]

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

[ronrsnfld]

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.

[Mic1979]

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")?

[ronrsnfld]

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.

[Mic1979]

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.

[ronrsnfld · Accepted solution]

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.

[Mic1979]

Hello

thanks for your answer, here the link to the sample file:

https://docs.google.com/spreadsheets/d/1_MFyOPpUwjy5Epy7c5LcY7-QSb-ZUEtv/edit?usp=drivesdk&ouid=1059...

[ronrsnfld]

Here is code that uses your two tables to produce your results:

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", "Join", JoinKind.LeftOuter),
    
    #"Move Join Down" = Table.FromColumns(
        Table.ToColumns(#"To Table") & 
        {{#table({},{})} & List.RemoveLastN(#"Join Core SubAssy"[Join],1)},Table.ColumnNames(#"Join Core SubAssy")),
    #"Expanded Join" = Table.ExpandTableColumn(#"Move Join Down", "Join", {"Disc", "Push Rod", "Spring 1", "Spring 2", "Core"})
in
    #"Expanded Join"

[ronrsnfld]

Yes, it is possible. It can be done with a Table.Join or Table.NestedJoin, along with some extra code to create the blank rows.

For more focused assistance, please supply your data tables as a text table that can be easily copy/pasted or attach a data file that can be used.