Results 1 to 10 of 10

Thread: Addition to group within a bill of material sturcture

  1. #1
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016

    Addition to group within a bill of material sturcture



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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





    Click image for larger version. 

Name:	eXCEL gURU.PNG 
Views:	138 
Size:	75.4 KB 
ID:	9056

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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?

  3. #3
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016
    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?!

  4. #4
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016
    Any ideas anyone??

  5. #5
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016
    I've included the tables now, any ideas would be great!...I just cant figure this out
    Attached Files Attached Files

  6. #6
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by horseyride View Post
    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 ??

  7. #7
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    See if attached helps.

    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.

    Code:
    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"

  8. #8
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Actually this one is a bit better, same theory

    Copy of Copy of Excel guru2.xlsx

    Code:
        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"

  9. #9
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016
    Thanks for looking at this, I will give it a whirl and report back!

  10. #10
    Seeker jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    8
    Articles
    0
    Excel Version
    2016
    It Works perfectly, thank you Horseyride and thankyou Excel Guru!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •