Sep 12 2021 10:37 PM
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:
Sep 15 2021 05:30 AM - edited Sep 15 2021 05:40 AM
Based on current dataset, the number of Children per parent does not look correct?
Comp3 has 100 children and
Comp3.1 has 20 children?
Sep 15 2021 08:59 AM
Sep 15 2021 09:09 AM
So you're saying Comp3 has 100 children, its just not showing in the current sample dataset.
Sep 15 2021 09:26 AM - edited Sep 15 2021 10:46 AM
No, not really. Comp3 has 100 children (named Comp3.1), and each of these children has 20 children (Comp3.1.1), which are grandchildren if Comp3.1. So Comp3 has 100 children Comp3.1, and 2000 grandchildren Comp3.1.1.
(Apart from that branch, there are also other children and grandchildren, like Comp3.2 and Comp3.2.1.)I did make an error, I see now, with Comp3.2 and Comp3.1.2, I'll fix that when I get home. But maybe you can see the general idea? Update: the data as such was correct, but it's more logical to name 3.1.2 3.2.1, so I attached a new workbook.
Sep 17 2021 08:31 AM - edited Sep 17 2021 08:38 AM
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.
Sep 17 2021 09:30 AM
Sep 17 2021 09:59 AM
In your sample COMP3 has two different parents - PIRO and PRIM. Is that misprint ?
Sep 17 2021 11:41 AM
Sep 17 2021 11:53 AM
You have to normalize your components first and establish a lowest unit of measure for each component, then you can group like components that go to like devices etc etc.
Sep 17 2021 12:24 PM
Sep 17 2021 12:34 PM
Let's make coffee an example.
a cup of coffee has several components:
coffee
creamer
sweetener
the lowest unit of measure when you're making coffee is how many scoops to make 1 cup of coffee do you need to put in the coffee maker maybe 1 or 2 scoops of grounds to make 4 cups
usually creamer is two teaspoons variance of +/- 1
and usually 2 packets of sweetener variance of +/- 1
so for each device what is the usual components and their usual lowest unit of measure
so if you are making 10 devices you would be using x number of like components example screws how many screws do you need to assemble a pc etc..
the reason for lowest unit of measure is for easy costing
when you buy corned beef for example each can is a fixed ounces and each case is say 24 cans etc.
so if you think about it all products in the market are packaged in like unit of measure.
can you imagine selling a case of soft drink 3 two litter bottles 4 8 ounces, 10 12 ounces etc
how do you cost that?
Sep 17 2021 01:22 PM
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?
Sep 17 2021 10:13 PM
Sep 18 2021 07:39 AM - edited Sep 18 2021 10:52 AM
@Yea_So @Sergei Baklan Maybe the following example is easier to understand (see also attached workbook):
So I have as sellable products a ToyCarSet1, a ToyCarSet2, the ToyCars individually, and Wheels which I sell as spare parts (see Table "Sales" bottom left). And I have the sales qties for these.
Above this, I have the Bills of Materials (in multiple levels). So a ToyCarSet1 contains 10 ToyCars, a ToyCarSet2 contains 2 Toy Cars and 1 spare Tyre (which is a subpart of a Wheel). In turn, a ToyCar contains 1 Top, 1 Bottom, 2 Bumpers, 4 Wheels etc. In turn, a Wheel contains 1 CenterPlate and 1 Tyre, which in turn contains 1 OuterTyre and 1 InnerTyre.
So for the end result, eg. for the OuterTyre, I need to multiply the number of OuterTyres in a Tyre with the number of Tyres in a Wheel, with the total number of wheels used (namely 23 sold separately as spare parts, plus 4 times 1000 as part of individually sold Toy Cars, plus 4 times 2 times 200 as parts of Toy Cars sold in ToyCarSet2, etc.).
(Edited to correct a calculation mistake, screenshot and workbook modified)
Sep 18 2021 07:57 AM
Thank you. The idea is clear. It's not clear how to solve, at least for me.
Sep 18 2021 11:01 AM
@Sergei Baklan @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!
Sep 18 2021 11:05 AM
Like I mentioned before, you have to normalize the lowest unit of measure for each part in order to accurately cost them for example:
If you ordered tires for the toy car, when you order toy car tires, how much is in a box of tires, and how much does a box cost you. So if the box of toy car tires costs 100 per box contains 100 tires per box you divide it cost / qty = 1.00 for each tire x 4 for each toy car that's 4.00 per toy car x number of toy car you're going to manufacture = 400.00 per 100 toy cars
so you're data model lacks that table the costing table for each part of one product.
Sep 18 2021 11:09 AM
Sep 19 2021 11:56 AM
SolutionI 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.