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 Yea_So I got somewhere using a chain of identical queries, one referring to the other.
Basically I merge the query with itself, looking up the subparts of the subparts. I then multiply the quantities of the lowest level with the in-between-level, remove the in-between-level, rename, and end up with a new table which has the same format as the original one, but with the data one level flattened.
This is the first loop:
let
Source = Excel.CurrentWorkbook(){[Name="tBOM"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", type text}, {"Subpart", type text}, {"# Subparts in Part", Int64.Type}}),
// Merge with self and expand grandchildren level
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Subpart"}, #"Changed Type", {"Part"}, "Changed Type", JoinKind.LeftOuter),
#"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Subpart", "# Subparts in Part"}, {"Subpart.1", "# Subparts in Part.1"}),
//Multiply qties of children with qties of grandchildren to obtain total qties of grandchildren in original parent
#"Added Custom" = Table.AddColumn(#"Expanded Changed Type", "Total # Subparts", each [#"# Subparts in Part"] * [#"# Subparts in Part.1"]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Total # Subparts", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"# Subparts in Part.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Total # Subparts", "# Subparts in Part.1"}}),
//If grandchild is null, then take child and child qty instead
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "SubPart.1 Merged", each if [Subpart.1] <> null then [Subpart.1] else [Subpart]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "# Subparts in Part.1 Merged", each if [#"# Subparts in Part.1"] <> null then [#"# Subparts in Part.1"] else [#"# Subparts in Part"]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"# Subparts in Part.1 Merged", Int64.Type}}),
//Make the table exactly as before this loop (but with one level flattened of course)
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"Subpart", "# Subparts in Part", "Subpart.1", "# Subparts in Part.1"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"SubPart.1 Merged", "Subpart"}, {"# Subparts in Part.1 Merged", "# Subparts in Part"}})
in
#"Renamed Columns1"The second loop then refers to this query as source, and does exactly the same, etc.
There are still some issues though.
One is that I don't know when to stop the chain, or how to make it loop until a certain condition is reached.
The other is that the data I end up with is correct, but incomplete.
My result:
The desired result:
So I end up with all the products that have no children, in the correct quantities, but all the upstream products are missing.
Any help, even just high-level in terms of a strategy that would work or a point that I'm missing, would be greatly appreciated!
That's great, thank for sharing. I'd only suggest to expand on any number of levels.
First, let create the function which calculates number of subparts in previous level. That is exactly your query defines as fntCalc() function:
( tCalc as table ) =>
let
Source = tCalc,
// NEXT IS INITAL QUERY, SKIPPED HERE
// ...
in
#"Renamed Columns1"Next function fntCalcSubParts() iterates level by level and stops iterations if previous level table is identical to one on current level. This is function with recursion:
( tCalc as table ) =>
let
nextLevel = fntCalc( tCalc )
in
if tCalc = nextLevel
then nextLevel
else fntCalcSubParts (nextLevel)Use it to calculate final subparts table
let
Source = Excel.CurrentWorkbook(){[Name="tBOM"]}[Content],
nextLevel = fntCalcSubParts( Source )
in
nextLevelAnd use above as source for you tResult query.
All above is in Variant 02 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 🙂. 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!
