Forum Discussion

Post Partisan

1 year ago

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 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.

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.

13 Replies

ronrsnfld
Super User
1 year ago
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.

Post Partisan

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=105984722371469999117&rtpof=true&sd=true

Super User

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"

Post Partisan

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.

Super User

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
Post Partisan
1 year ago
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
  Super User
  1 year ago
  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
    Post Partisan
    1 year ago
    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
Mic1979
Post Partisan
1 year ago
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
  Super User
  1 year ago
  See my previous response.
  
  For more focused assistance, you will need to provide a usable data set and the code which reproduces the error.

Related content