Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
awitte58
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

 

BOM Example.png

2 ACCEPTED SOLUTIONS
wdx223_Daniel
Super User
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]))))})

View solution in original post

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1712555121375.png

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.

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1712555121375.png

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
Super User
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]))))})

lbendlin
Super User
Super User

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors