Addition to group within a bill of material sturcture

[jilbobagins]

Howdi,

New to the forum, and I've had a good old search but cannot find the answer to the query below.

Within a BOM structure table (Example on the LH table) I'm looking to automate changes (Additions, Change, Deletions) by another smaller table (shown on the RH table). I can manage the deletions (take qty to 0, as I want to retain the record) and any changes, however the additions is proving difficult as they need to inserted at the bottom of parent part (used on) which is within a structured bill of material. No amount of sorting seems to be able to fix this!

Any help would be greatly appreciated

Thanks

 

[horseyride]

Why cant you start the change series index at 10,000 instead of 1; then when you merge change series table into the BOM structure table, sort first by PArt Number then by combined Serial/Change Series, and the changes will be on the bottom of each section?

 

[jilbobagins]

Its more about adding data to an existing group (beneath the last record within that group), that group is the used on column in the first table...im not sure what your suggesting would work?!

 

[jilbobagins]

Any ideas anyone??

 

[jilbobagins]

I've included the tables now, any ideas would be great!...I just cant figure this out

 

[jilbobagins]

Why cant you start the change series index at 10,000 instead of 1; then when you merge change series table into the BOM structure table, sort first by PArt Number then by combined Serial/Change Series, and the changes will be on the bottom of each section?

Could you show me what you mean with the above ??

 

[horseyride]

See if attached helps.

View attachment Copy of Copy of Excel guru.xlsx

Its a little unclear what you want to do with the remaining columns like Series, Itemno, etc, and if you need to renumber the sequence, but you can fiddle with it
Basically, I'm grouping the first table to get the maximum Serial. Then I am grouping the second table, adding an index by 0.01's, then adding that to the maximum serial from the first part. Resort and done.

let Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Grouped = Table.Group(Source1, {"Used On "}, {{"MaxSerial", each List.Max([Serial]), type number}}),

Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source2, each ([change type] = "ADD")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"change used on"}, {{"Count", each Table.AddIndexColumn(_, "Index",.01,.01), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"change serial", "change type", "change part number", "change description", "change qty", "change details", "Index"}, {"change serial", "change type", "change part number", "change description", "change qty", "change details", "Index"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Count",{"change used on"},Grouped,{"Used On "},"Grouped",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Grouped", {"MaxSerial"}, {"MaxSerial"}),
#"Added Custom" = Table.AddColumn(#"Expanded Table1", "Serial", each [Index]+[MaxSerial]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"change used on", "Used On "}, {"change part number", "Part Number"}, {"change description", "Description"}, {"change qty", "Qty"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"change serial", "change type", "change details", "Index", "MaxSerial"}),

Combined = Table.Combine({Source1, #"Removed Columns"}),
#"Sorted Rows" = Table.Sort(Combined,{{"Serial", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Level"})

in #"Filled Down"

 

[horseyride]

Actually this one is a bit better, same theory

View attachment Copy of Copy of Excel guru2.xlsx

    let Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grouped = Table.Group(Source1, {"Used On "}, {{"MaxSerial", each List.Max([Serial]), type number}, {"MaxItem", each List.Max([item no]), type number}}),

    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source2, each ([change type] = "ADD")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"change used on"}, {{"Count", each Table.AddIndexColumn(_, "Index",.001,.001), type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"change serial", "change type", "change part number", "change description", "change qty", "change details", "Index"}, {"change serial", "change type", "change part number", "change description", "change qty", "change details", "Index"}),

    #"Merged Queries" = Table.NestedJoin(#"Expanded Count",{"change used on"},Grouped,{"Used On "},"Grouped",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Grouped", {"MaxSerial", "MaxItem"}, {"MaxSerial", "MaxItem"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Serial", each [Index]+[MaxSerial]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "item no", each [Index]*10000+[MaxItem]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"change used on", "Used On "}, {"change part number", "Part Number"}, {"change description", "Description"}, {"change qty", "Qty"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"change serial", "change type", "change details", "Index", "MaxSerial","MaxItem"}),

    Combined = Table.Combine({Source1, #"Removed Columns"}),
    #"Sorted Rows" = Table.Sort(Combined,{{"Serial", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Level"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Serial"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Serial", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Serial", "Level", "item no", "Used On ", "Part Number", "Description", "Qty"})
    in  #"Reordered Columns"

 

[jilbobagins]

Thanks for looking at this, I will give it a whirl and report back!

 

[jilbobagins]

It Works perfectly, thank you Horseyride and thankyou Excel Guru! :whoo: