Finding all components a sales item consists of

[magjon]

Hi,

I need help creating a table of all components my sales items consists of. I have a Bill of Materials table which looks likes this:

If both item A and B are sales items, I want to find all components and put in a table. A component can in turn consist of other components, this is indicated by a 1 in type column.

The result for this example would look like this:

I would greatly appreciate any help with this!

/Magnus

[Phil_Seamark · Accepted solution]

Hi @magjon

This code will work on your sample but doesn't have fantastic recursion.  It's hardcoded to just 2 levels.  I'm sure this can be made more dynamic but have a look at let me know what you think.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJ0cgaSxnqmQNJAKVYHIuoExEZAbAgX8XBzB5IIVU4QvS5AykTPEEXYzd0Dqh0h5oxiIIgX6gHmghXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, Component = _t, qty = _t, #"type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Component", type text}, {"qty", type number}, {"type", Int64.Type}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [type] = 1),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Component"},#"Changed Type",{"Item"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Item", "Component", "qty", "type"}, {"NewColumn.Item", "NewColumn.Component", "NewColumn.qty", "NewColumn.type"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{ "qty", "type", "NewColumn.Item"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Component", "ComponentLink"} , {"NewColumn.Component", "Component"}, {"NewColumn.qty", "qty"}, {"NewColumn.type", "type"}}),

    #"X" = Table.SelectRows(#"Removed Columns", each [NewColumn.type] = 1),
    #"Merged Queries2" = Table.NestedJoin(#"X",{"NewColumn.Component"},#"Changed Type",{"Item"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"Item", "Component", "qty", "type"}, {"NewColumn.Item", "NewColumn.Component.1", "NewColumn.qty.1", "NewColumn.type.1"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn2",{"Component", "NewColumn.Component", "NewColumn.qty", "NewColumn.type", "NewColumn.Item"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"NewColumn.Component.1", "Component"}, {"NewColumn.qty.1", "qty"}, {"NewColumn.type.1", "type"}}),


    #"Appended Query" = Table.Combine({ #"Renamed Columns", #"Changed Type"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Appended Query",{"ComponentLink"}),
    #"Final" = Table.Combine({ #"Renamed Columns2" ,#"Removed Columns1" })

in
     #"Final"

[magjon]

Hi Phil,

Thank you! With some modification I was able to turn your code into a function I could use recursively. It solved my problem. again thanks! 

/Magnus

[Phil_Seamark]

Hi @magjon

I was going to revisit this tonight to build a recursive version.  Did you limit to N number of levels or could it look down many levels?

[magjon]

Hi,

It looks down many levels.

This is the function I made:

let
    Source = (NewTable, ToTable, OrgTable) =>
    
let

    #"Filtered Rows" = Table.SelectRows(NewTable,each [type] = 1),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows",{"Component"},OrgTable,{"Item"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Item", "Component", "qty", "type"}, {"NewColumn.Item", "NewColumn.Component", "NewColumn.qty", "NewColumn.type"}),
    #"Lägg till egen" = Table.AddColumn(#"Expanded NewColumn", "newqty", each [qty]*[NewColumn.qty]),
    #"Ändrad typ" = Table.TransformColumnTypes(#"Lägg till egen",{{"newqty", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Ändrad typ",{ "Component", "qty", "type", "NewColumn.Item", "NewColumn.qty"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewColumn.Component", "Component"}, {"newqty", "qty"}, {"NewColumn.type", "type"}}),
    #"Appended Query" = Table.Combine({ #"Renamed Columns", ToTable}),
    Result = if Table.First(Table.SelectRows(#"Renamed Columns",each [type] = 1))=null then #"Appended Query" else HittaStruktur(#"Renamed Columns",#"Appended Query",OrgTable)
in                
    Result
in
    Source

/Magnus