Finding the structure level from parent and child bom structure

jilbobagins

jilbobagins

Member
Joined
Apr 11, 2019
Messages
80
Reaction score
0
Points
6
Excel Version(s)
2016
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!!


application.pdf
 

Attachments

  • BOM_CHECK.xlsx
    11.5 KB · Views: 23
Are you just trying to grab [Level] from [Parent] and [Child] for each row?
Recursive method. There would be a List.Accumulate method
View attachment 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"
 
works fine for me. Refresh rightmost table
 
Last edited:
Thanks once again Horseyride!! You are a superstar !!
 
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
 
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?
 
View attachment 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"
 
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

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 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(#"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"
 
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
 
Your right it doesn’t, I got to the limits of my current skills!!

I’ve just performed your code on a BOM many thousands of lines long....it got the duplicated parent correct however it didn’t get its child items right?? Any ideas
 
post a sample. along with what you think output needs to be for questionable items.
 
I‘ll update the sample BOM to demonstrate. But yes if the child part is a parent and then as a parent used in a different level assembly (all possible stuff) it’s children’s levels refer to the first instance of that record assembly
 
You must log in or register to reply here.
Back
Top