Parent-child flattening (multiple levels)

[bartvana]

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.

[#2]

@bartvana 

Based on current dataset, the number of Children per parent does not look correct?

Comp3 has 100 children and 

Comp3.1 has 20 children?

[#3]

The 20 children of COMP3.1 are only related to COMP3.1, not upstream.
So Comp3 has 100 x 20 x Comp3.1.1.
The quantities only refer to 1 level lower.

[#4]

@bartvana 

So you're saying Comp3 has 100 children, its just not showing in the current sample dataset.

[#5]

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.

[#6]

@bartvana 

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.

[#7]

"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!

[#8]

@bartvana 

In your sample COMP3 has two different parents - PIRO and PRIM. Is that misprint ?

[#9]

so then PRIM should be 4?

[#10]

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?

[#11]

@bartvana 

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.

[#12]

I'm sorry, I don't quite follow, can you give an example please?

[#13]

@bartvana 

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?

[#14]

@bartvana 

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?

[#15]

No, maximum number of levels is not known in advance.

[#16]

@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)

[#17]

@bartvana 

Thank you. The idea is clear. It's not clear how to solve, at least for me.

[#18]

@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!

[#19]

@bartvana 

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.

[#20]

It might be me but I still don't follow, I'm not trying to calculate the cost, I'm trying to calculate quantities only. So in this case the lowest unit of measure would be, for each product, 1. (1 unit).