Forum Discussion
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): how would you approach this?
I have a dataset with parents, children and quantities of children in the parent.
I also have sales figures (quantities) of (some of the) parents and children. Some components are sold both as-is, and as part of a parent.
What I need is the quantity of all components, flattened. See below (and attached workbook):
Any help to put me on the right track would be greatly appreciated!
Note that:
- Some of the children contain grandchildren, which in turn contain grand-grandchildren, and so on, and the maximum depth is unknown beforehand.
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.
30 Replies
Points to Ponder in your Parent Child hierarchy
if you manufacture 500 toy cars
and you can only purchase toy wheels by cases
each case contains 20 boxes of wheels
each box contains 100 wheels
How many cases of wheels do you have to buy in order to fulfill your manufacturing needs for the 500 toy cars.
considering each car only needs 4 wheels each never mind the cost of each wheel but the question is how many cases do you need to order for the 500 toy cars
and it boils down to the lowest unit of measure.
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.
