Forum Discussion

Mic1979's avatar
Mic1979
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 p...
Need Help
Mic1979's avatar
Mic1979
Post Partisan
1 year ago

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's avatar
ronrsnfld
Super User
1 year ago

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.