Total quantities from an indented BOM list - make the code dynamic based on the number of levels

[Anonymous]

I have some code which finds me the total quantity of items that are in an indented Bill of Materials (BOM) list. The BOM list is a hierarchy of parent and child relationships, and these relationships can be interpreted through the column called "LEVEL". If a component (let's say a spoke in the image example below) a has a level of 1.2.1.1, it means it is on the 4th level (4 delimiters), and it would be 4 indents in within the compiled BOM. It is the 1st component (.1) of the Wheel component, which is at level 1.2.1. The wheel is the first component of the Frame Assembly, which is the second component of the Bike. To find the total number of spokes we need, we need to multiple each of the component quantities from each level. So we have 25 spokes to a wheel, 2 wheels to a Frame Assembly, and 1 frame assembly to a Bike. So we need 25 * 2 * 1 * 1 = 50 spokes in total.

Now, I have some code which can calculate all these quantities. But it is quite dumb as the number of delimiters, or levels, is hard coded. I have manually added each column needed. If I have a BOM with 20 levels, my code will not work. So I need to make it dynamic based on the number of levels there are. I don't know where to start though. Is anyone able to help with this?

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZJLDsQwCEPv0nVVNQ759CxV73+NIUgjEXtRKX4lgCHve5Tj9O87/XRtZ1aiQbqSNtKNdHddkx70f7oGEngcWAbldjIziB57JqvLJ4PV5hZh1EcxtW7hlqLYsCP2XBpfW643D8u2ZTAZPARw8yYKVQHvBtD1ITzlUYAdgf2g8ZXOEV0rdXkrjioNwpFU49lgaPKhyUckrzvS5PEiQeiSWQYMrLH/Z/H9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LEVEL = _t, #"LEVEL QTY" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"LEVEL", type text}, {"LEVEL QTY", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
Back1 = Table.AddColumn(#"Added Index", "Back1", each Text.Combine(List.RemoveLastN(Text.Split([LEVEL],"."),1),".")),
Back2 = Table.AddColumn(#"Back1", "Back2", each Text.Combine(List.RemoveLastN(Text.Split([Back1],"."),1),".")),
Back3 = Table.AddColumn(#"Back2", "Back3", each Text.Combine(List.RemoveLastN(Text.Split([Back2],"."),1),".")),
Back4 = Table.AddColumn(#"Back3", "Back4", each Text.Combine(List.RemoveLastN(Text.Split([Back3],"."),1),".")),
Back5 = Table.AddColumn(#"Back4", "Back5", each Text.Combine(List.RemoveLastN(Text.Split([Back4],"."),1),".")),
Back6 = Table.AddColumn(#"Back5", "Back6", each Text.Combine(List.RemoveLastN(Text.Split([Back5],"."),1),".")),
Back7 = Table.AddColumn(#"Back6", "Back7", each Text.Combine(List.RemoveLastN(Text.Split([Back6],"."),1),".")),
    #"Merged Queries" = Table.NestedJoin(Back7, {"Back1"}, Back7, {"LEVEL"}, "Back7.1", JoinKind.LeftOuter),
    #"Expanded Back7.1" = Table.ExpandTableColumn(#"Merged Queries", "Back7.1", {"LEVEL QTY"}, {"Back1.LEVEL QTY"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Back7.1", {"Back2"}, #"Expanded Back7.1", {"LEVEL"}, "Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"LEVEL QTY"}, {"Back2.LEVEL QTY"}),
    #"Merged Queries2" = Table.NestedJoin(#"Expanded Sorted Rows", {"Back3"}, #"Expanded Sorted Rows", {"LEVEL"}, "Expanded Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries2", "Expanded Sorted Rows", {"LEVEL QTY"}, {"Back3.LEVEL QTY"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded Expanded Sorted Rows", {"Back4"}, #"Expanded Expanded Sorted Rows", {"LEVEL"}, "Expanded Expanded Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries3", "Expanded Expanded Sorted Rows", {"LEVEL QTY"}, {"Back4.LEVEL QTY"}),
    #"Merged Queries4" = Table.NestedJoin(#"Expanded Expanded Expanded Sorted Rows", {"Back5"}, #"Expanded Expanded Expanded Sorted Rows", {"LEVEL"}, "Expanded Expanded Expanded Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Expanded Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries4", "Expanded Expanded Expanded Sorted Rows", {"LEVEL QTY"}, {"Back5.LEVEL QTY"}),
    #"Merged Queries5" = Table.NestedJoin(#"Expanded Expanded Expanded Expanded Sorted Rows", {"Back6"}, #"Expanded Expanded Expanded Expanded Sorted Rows", {"LEVEL"}, "Expanded Expanded Expanded Expanded Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Expanded Expanded Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries5", "Expanded Expanded Expanded Expanded Sorted Rows", {"LEVEL QTY"}, {"Back6.LEVEL QTY"}),
    #"Merged Queries6" = Table.NestedJoin(#"Expanded Expanded Expanded Expanded Expanded Sorted Rows", {"Back7"}, #"Expanded Expanded Expanded Expanded Expanded Sorted Rows", {"LEVEL"}, "Expanded Expanded Expanded Expanded Expanded Sorted Rows", JoinKind.LeftOuter),
    #"Expanded Expanded Expanded Expanded Expanded Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries6", "Expanded Expanded Expanded Expanded Expanded Sorted Rows", {"LEVEL QTY"}, {"Back7.LEVEL QTY"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Expanded Expanded Expanded Expanded Sorted Rows",null,1,Replacer.ReplaceValue,{"Back1.LEVEL QTY", "Back2.LEVEL QTY", "Back3.LEVEL QTY", "Back4.LEVEL QTY", "Back5.LEVEL QTY", "Back6.LEVEL QTY", "Back7.LEVEL QTY"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "TOTAL QTY.", each [LEVEL QTY]*[Back1.LEVEL QTY]*[Back2.LEVEL QTY]*[Back3.LEVEL QTY]*[Back4.LEVEL QTY]*[Back5.LEVEL QTY]*[Back6.LEVEL QTY]*[Back7.LEVEL QTY]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

[wdx223_Daniel]

= #table(Table.ColumnNames(Source)&{"Total QTY"},Record.ToList(List.Accumulate(Table.ToRows(#"Changed Type"),[],(x,y)=>Record.TransformFields(x,{y{0},each y&{Record.FieldOrDefault(x,Text.BeforeDelimiter(y{0},".",{0,1}),{}){2}?*y{1}??y{1}}},2))))

the parent level must present before children level, or will get wrong result.