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
  • ronrsnfld's avatar
    ronrsnfld
    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.

     

     

     

Mic1979's avatar
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's avatar
    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's avatar
      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

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        1 year ago

        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.