Results 1 to 10 of 13

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Acolyte jilbobagins's Avatar
    Join Date
    Apr 2019
    Posts
    57
    Articles
    0
    Excel Version
    2016

    Finding the structure level from parent and child bom structure

    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
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    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,370
    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
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    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
    57
    Articles
    0
    Excel Version
    2016
    Thanks once again Horseyride!! You are a superstar !!

  6. #6
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    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
    57
    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?

Posting Permissions

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