Forum Discussion
Solved
Parent-child flattening (multiple levels)
Hello, I have an interesting problem to work out and I'm sure there's a very efficient and elegant solution using Power Query. Before I start tinkering myself (in probably not the most efficient way...
I didn't dive a deep into calculations, here is only suggestion for iterations.
We keep as it is now tBOM and tCalc1.
Levels are iterated by fntCalcQty:
( tCalc as table, tBasis as table, tAccumulate as table ) => let nextLevel = fnCalc( tCalc, tBasis ), addTable = Table.Combine( {tAccumulate, nextLevel} ) in if Table.RowCount( nextLevel ) = 0 then tAccumulate else fntCalcQty (nextLevel, tBasis, addTable)It iterates till first empty table appears.
Final result is calculated as
let Source = fntCalcQty( tCalc1, tBOM, tCalc1), addSales = Table.Combine( {Source, tSalesRenamed} ), #"Grouped Rows" = Table.Group( addSales, {"Subpart"}, {{"TotalQty", each List.Sum([TotalQty]), type nullable number}} ), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Subpart", Order.Ascending}}) in #"Sorted Rows"Above is in Variant 03 group in attached.
No, it's not a misprint. Maybe the term Parent/Child is misleading here. You can see it as follows:
PIRO is an electronic device that contains, among other parts, COMP3 (let's say COMP3 is a meter of wire, so 0.5 qty of the item COMP3 is half a meter of wire).
PIRM is another electronic device that also contains, among other parts, COMP3 (in the case of PIRM 10 meters of wire).
One COMP3, in turn, contains 100 COMP3.1, et cetera.
(The data is fictitious so don't go too far in the details of this example, but that's how the numbers should be understood).
Does that make sense?
PIRO is an electronic device that contains, among other parts, COMP3 (let's say COMP3 is a meter of wire, so 0.5 qty of the item COMP3 is half a meter of wire).
PIRM is another electronic device that also contains, among other parts, COMP3 (in the case of PIRM 10 meters of wire).
One COMP3, in turn, contains 100 COMP3.1, et cetera.
(The data is fictitious so don't go too far in the details of this example, but that's how the numbers should be understood).
Does that make sense?
I see, thank you. I thought about building hierarchy to summaries by its branches, but here that won't work. Is maximum number of levels known or it differ from case to case?
- No, maximum number of levels is not known in advance.