cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Flattening Parent-Child Hierarchy for Bill of Materials

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

2 ACCEPTED SOLUTIONS
Super User

=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]))))})

Community Support

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}}),
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.

3 REPLIES 3
Community Support

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}}),
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.

Super User

=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]))))})

Super User

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors