Flattening Parent-Child Hierarchy for Bill of Materials

[awitte58]

I have a BOM (Bill Of Materials) that I need to determine the total quantity of parts to purchase. 

From my research I think I need to flatten the parent-child hierarchy. Can anyone help with that?  

The table shows the data I am working with. I am unsure how to modify the first column to break it out simply. 

I've shown an example below to help explain how the math works. 

EX: Part Number 15

(1 [Qty of SubAssyA] * 2 [Qty of SubAssyA_SubAssy5] * 2 [Qty of SubAssyA_SubAssy5_Part7]) + (1 [Qty of SubAssyB] * 2 [Qty of SubAssyB_SubAssy10] * 1 [Qty of SubAssyB_SubAssy10_Part1] ) + ( 1 [Qty of SubAssyB] * 8 [Qty of SubAssyB_Part7] ) = 14

Or

(1*2*2) + (1*2*1) + (1*8) = 14

[wdx223_Daniel · Accepted solution]

=Table.Group(YourTable,"Part Number",{"QTY",each List.Sum(List.Transform([Level],each let a=Text.Split(Text.From(_),".") in List.Product(List.Transform(List.Positions(a),each YourTable{[Level=Text.Combine(List.FirstN(a,_+1),".")]}[Qty]))))})

[v-xinruzhu-msft · Accepted solution]

Hi @awitte58 

Thanks for the solutions @wdx223_Daniel  and @lbendlin  provided, and i want to offer some more information for you to refer to.

You can add a custom column (I named it Total_qty) and input the following code.

let a=Table.AddColumn(#"Changed Type"(You last step name),"Value",each List.Product(Table.SelectRows(#"Changed Type"(You last step name),(x)=>Text.Contains([Model Name],x[Model Name]))[Qty]))
in List.Sum(Table.SelectRows(a,(x)=>x[Part Number]=[Part Number])[Value])

Output

And you can refer to the following M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVMxDoMwDPxLZgZyDrQdCx+o1BGhqn1CaYf+vthAgmOUgUic7bPv4gyDu39fV1c5L99YLcDj9nx/GAoWBkPtfEDjNEOtTWeGk4b5mKZfM6NnzbNFYv/LcWUapC4k8ETeFzqIOhQYeERPhQQRHAoJIr3Zz9DJz66ii2pRa3xlEpGaI4VisXQ5Kk5moZRBRus6GJNTLZelAswGbwuYA5DlUTjbBNIyNpMRLI/sWGPxaGk2j+xSa/N5h6BWsGdFCkhXgP2AffIuWNw61m+eBrObKXS83Fkc+ePI4rQ+K3Lj+Ac=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Model Name" = _t, #"Part Number" = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Model Name", type text}, {"Part Number", Int64.Type}, {"Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Total_Qty", each let a=Table.AddColumn(#"Changed Type","Value",each List.Product(Table.SelectRows(#"Changed Type",(x)=>Text.Contains([Model Name],x[Model Name]))[Qty]))
in List.Sum(Table.SelectRows(a,(x)=>x[Part Number]=[Part Number])[Value])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Part Number] = 15))
in
    #"Filtered Rows"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel · Accepted solution]

=Table.Group(YourTable,"Part Number",{"QTY",each List.Sum(List.Transform([Level],each let a=Text.Split(Text.From(_),".") in List.Product(List.Transform(List.Positions(a),each YourTable{[Level=Text.Combine(List.FirstN(a,_+1),".")]}[Qty]))))})

[lbendlin]

Are you aware of this one?  Parent-Child Hierarchies with multiple parents in Power BI with Power Query (thebiccountant.com)