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.
Your narrative is ambiguous, I see in the screenshot that KOF has two children, and yet in the #children in Parent for PIRO says three.
Somehow either you are skipping details in your narrative maybe KOF has a secret or your narrative is not explained very well for any reader to comprehend the problem.
- "KOF ; PIRO ; 3" means: in every KOF there are 3 PIRO's. (Apart from all the rest that KOF may contain).
So if you see it like that, I think the narrative is coherent?
Thanks for looking into my problem!- so then PRIM should be 4?
In your sample COMP3 has two different parents - PIRO and PRIM. Is that misprint ?
- 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?
