Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Finding the structure level from parent and child bom structure

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

    Finding the structure level from parent and child bom structure



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

    Hi all,

    Im trying to find a method of creating the structure level from a Child and Parent Bill Of Material structure.

    Previous requests have managed to extract the Parent from a structure that includes the Level, however sometimes i receive BOM's without the level.

    I've attached an excel workbook below, with the level included to check against after

    any assistance is greatly appreciated!!


    Attached Files Attached Files

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Are you just trying to grab [Level] from [Parent] and [Child] for each row?
    Recursive method. There would be a List.Accumulate method
    bomcheck.xlsx

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Part Number", type text}, {"Child Part Number", type text}, {"Description", type text}, {"Qty", Int64.Type}}),
    
        Parent_List=List.Buffer(#"Changed Type"[Parent Part Number]),
        Child_List=List.Buffer(#"Changed Type"[Child Part Number]),
        Depth = (child as text, optional level as nullable number) =>
        let Parent = Parent_List{ List.PositionOf( Child_List, child )}
        in if List.PositionOf( Child_List, Parent ) = -1 then level else @Depth( Parent, level + 1 ),
    
        #"Level"= Table.AddColumn( #"Changed Type", "Level", each Depth( [Child Part Number],0), type number)
    
    in #"Level"

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,275
    Articles
    0
    Excel Version
    Office 365 Subscription
    There is no source data or query to check in that workbook.
    Ali
    Enthusiastic self-taught user of MS Excel!

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    works fine for me. Refresh rightmost table
    Last edited by horseyride; 2019-11-08 at 02:21 PM.

  5. #5
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    Thanks once again Horseyride!! You are a superstar !!

  6. #6
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    Note I dont think this approach work if the [child] repeats, and is a different number of levels down for each repeat. So, if Bolt123 is 3 levels down as part of Gizmo1, if Bolt123 is also part of Gizmo2 but 4 levels down, it would break because it would not differentiate between the two

  7. #7
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    Ah ok, we do have repetitions certainly when it comes to fasteners ( nuts, bolts & washers etc) wonder if I could add suffix to duplicates to make them unique then remove after?

  8. #8
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    bomcheck.2.xlsx

    This seems to do the trick, using two pass method -- determining level 1's then appending prefix and running the levels a second time
    Probably a more elegant method out there


    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Part Number", type text}, {"Child Part Number", type text}, {"Description", type text}, {"Qty", Int64.Type}}),
    
        Parent_List=List.Buffer(#"Changed Type"[Parent Part Number]),
        Child_List=List.Buffer(#"Changed Type"[Child Part Number]),
        Depth = (child as text, optional level as nullable number) =>
        let Parent = Parent_List{ List.PositionOf( Child_List, child )}
        in if List.PositionOf( Child_List, Parent ) = -1 then level else @Depth( Parent, level + 1 ),
    
        #"Level"= Table.AddColumn( #"Changed Type", "Level", each Depth( [Child Part Number],0), type number),
        #"Added Custom" = Table.AddColumn(Level, "Custom", each if [Level]=0 then [Parent Part Number] else if [Level]=1 then [Child Part Number] else null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
        #"Added Custom1" = Table.AddColumn(#"Filled Down", "Parent2", each [Custom]&":"&[Parent Part Number]),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Child2", each [Custom]&":"&[Child Part Number]),
    
        New.Parent_List=List.Buffer(#"Added Custom2"[Parent2]),
        New.Child_List=List.Buffer(#"Added Custom2"[Child2]),
        New.Depth = (New.child as text, optional New.level as nullable number) =>
        let New.Parent = New.Parent_List{ List.PositionOf( New.Child_List, New.child )}
        in if List.PositionOf( New.Child_List, New.Parent ) = -1 then New.level else @New.Depth( New.Parent, New.level + 1 ),
        #"Level2"= Table.AddColumn( #"Added Custom2", "Level2", each if [Custom] = [Child Part Number] then 1 else New.Depth( [Parent2],2), type number),
        #"Replaced Errors" = Table.ReplaceErrorValues(Level2, {{"Level2", 0}})
    in
        #"Replaced Errors"

  9. #9
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    49
    Articles
    0
    Excel Version
    2016
    I've had a go as well!!

    Obviously not to your standards...LOL

    It worked though when I added duplicate child items

    I'll have a play now

    PHP Code:
    Let
        Source 
    Excel.CurrentWorkbook(){[Name="Table2
    "
    ]}[Content],
        
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Part Number", type text}, {"Child Part Number", type text}, {"Description", type text}, {"Qty", Int64.Type}}),
        
    BOM Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
     
    //This next section find duplicate child parts and then merges index to make them a primary key to then establish the real level and the re-merge into query
     
        #"Grouped Rows" = Table.Group(BOM, {"Child Part Number"}, {{"Count", each Table.RowCount(_), type number}, {"mytable", each _, type table}, {"Min Index", each List.Min([Index]), type number}}),
        #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 1),
        #"Expanded mytable" = Table.ExpandTableColumn(#"Filtered Rows", "mytable", {"Index"}, {"Index"}),
        #"Added Conditional Column" = Table.AddColumn(#"Expanded mytable", "Custom", each if [Index] = [Min Index] then "Delete" else "Keep"),
        #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = "Keep")),
        #"Duplicated Column" = Table.DuplicateColumn(#"Filtered Rows1", "Index", "Index - Copy"),
        #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Index - Copy", type text}}, "en-GB"),{"Child Part Number", "Index - Copy"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged"),
     
    //Merge duplicate values with original BOM
     
        
    Custom1 Table.NestedJoin(#"Merged Columns",{"Index"},BOM,{"Index"},"BOM",JoinKind.RightOuter),
        #"Expanded BOM" = Table.ExpandTableColumn(Custom1, "BOM", {"Parent Part Number", "Child Part Number", "Description", "Qty", "Index"}, {"Parent Part Number", "Child Part Number", "Description", "Qty", "Index.1"}),
        #"Added Conditional Column1" = Table.AddColumn(#"Expanded BOM", "Custom.1", each if [Merged] <> null then [Merged] else [Child Part Number]),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column1",{"Parent Part Number", "Description", "Qty", "Index.1", "Custom.1"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom.1", "Child Part Number"}, {"Index.1", "Index"}}),
     
    //Establish through lists and parameters the level of the structure
     
        
    Parent_List=List.Buffer(#"Renamed Columns"[Parent Part Number]),
        
    Child_List=List.Buffer(#"Renamed Columns"[Child Part Number]),
        
    Depth = (child as textoptional level as nullable number) =>
        
    let Parent Parent_List{ List.PositionOfChild_Listchild )}
        
    in if List.PositionOfChild_ListParent ) = -1 then level else @DepthParentlevel ),
        
    #"Level"= Table.AddColumn(#"Renamed Columns", "Level", each Depth( [Child Part Number],0), type number),
     
    //Remove Delimter from duplicated child part
     
        #"Extracted Text Before Delimiter" = Table.TransformColumns(Level, {{"Child Part Number", each Text.BeforeDelimiter(_, "#"), type text}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Extracted Text Before Delimiter",{"Index", "Level", "Parent Part Number", "Child Part Number", "Description", "Qty"})
    in
        
    #"Reordered Columns" 

  10. #10
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    92
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    I have standards?

    Kidding aside, I'm not sure your method works when the duplicate child is itself the parent to another part. Mine, as ugly as it is, seems to

Page 1 of 2 1 2 LastLast

Posting Permissions

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