Forum Discussion
Parent-child flattening (multiple levels)
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.
SergeiBaklan This is exciting, we're really getting there! I nailed the result by changing my approach, so that part of the problem (the function in itself) is now solved.
But now it needs to be improved using a conditional loop, somewhat similar to what you proposed, but what I do now is calculate 5 separate tables and append them afterwards, so I don't really know how to implement your function strategy.
Could you give a hand please?
So the result is now exactly what I wanted:
And I do this using one function and the following queries:
- tSales and tBOM are simply the basic data, and tCalc1 fetches the sales quantities for the parents (so it's a bit different from the rest, because it doesn't fetch subparts)
- tCalc2 to tCalc5 are the daisychain of the function. They each result in a table that's fed into the next loop, and at the end I append all these tables. I know I'm done when the last table is empty.
- tSalesRenamed is simply tSales with the columns renamed so I can append it easily to tCalc1-5.
- tResult is tCalc1-5 and tSalesRenamed all appended (and grouped)
So basically, what I need, in plain English, is:
- Start from the result of tCalc1
- Execute fnCalc and store the resulting table somewhere
- Execute fnCalc on the result from the previous step, and store that table also
- Repeat 3 until the result from fnCalc is empty
- Append all the stored tables (and do some final manipulations which are no problem)
If we can get this done, we're completely there!
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.
bartvana great, glad it helped
- so how off was your narrative in describing your problem?
- Super fabulous, thank you, exactly what I needed!! (A deep dive into the math wasn't necessary, it's just the iteration I was wondering about, so you gave the perfect answer).
I'll implement this asap in my real data and see what it gives. In any case, I learnt a lot and the general strategy is the solution to my problem. You've been very helpful and completely made my day 🙂.
