Find Parents Quantity

[gheyman]

FYSA: These are parts that are listed in a drawing to show how something is assembled

I have Six columns of data [Index], [Level], [Make/Buy],[Assembly], [Material] and [Quantity]

1​	1	Make	1000614-201	1000614-201	1​
2​	2	Buy	1000614-201	00005300	1​
3​	2	Buy	1000614-201	6455062	1​
4​	2	Make	1000614-201	6456677	2​
5​	3	Make	6456677	6456678	1​
6​	4	Buy	6456678	6456678-B	1​
7​	3	Buy	6456677	6456676	1​
8​	3	Buy	6456677	00026614	1​
9​	3	Buy	6456677	1033030-001	1​
10​	2	Buy	1000614-201	6456679	1​
11​	2	Buy	1000614-201	6456683	1​
12​	2	Buy	1000614-201	6456688	1​
13​	2	Buy	1000614-201	30-07-131	1​
14​	2	Buy	1000614-201	1033940-001	2​
15​	2	Buy	1000614-201	B0612-2-041	1​
16​	2	Buy	1000614-201	8000632	1​
17​	2	Buy	1000614-201	8000813-006	7​
18​	2	Buy	1000614-201	8000811-001	4​
19​	2	Buy	1000614-201	B0612-2-014	1​
20​	2	Buy	1000614-201	8000767-003	1​
21​	2	Buy	1000614-201	NAS620C5	2​
22​	2	Buy	1000614-201	M12133/5-1P	1​
23​	2	Buy	1000614-201	MS90707-4012	1​
24​	2	Buy	1000614-201	NAS620C5L	2​

I want to, some how in Power Query, get a new column that had what's called the Unit Qty. To get this the formula /steps need to look as each rows parent (or next level up) and multiply its ext quantity by the rows Quantity. So the results would look like this (Which I did manually)

INDEX	LEVEL	MAKE/BUY	ASSEMBLY	MATERIAL	QUANTITY	UNTI QUANTITY
1​	1	Make	1000614-201	1000614-201	2​	2​
2​	2	Buy	1000614-201	00005300	1​	2​
3​	2	Buy	1000614-201	6455062	1​	2​
4​	2	Make	1000614-201	6456677	2​	4​
5​	3	Make	6456677	6456678	1​	2​	I am multuplied by my Parents Unit Quantity
6​	4	Buy	6456678	6456678-B	1​	2​
7​	3	Buy	6456677	6456676	1​	2​
8​	3	Buy	6456677	00026614	1​	2​
9​	3	Buy	6456677	1033030-001	1​	2​
10​	2	Buy	1000614-201	6456679	1​	2​
11​	2	Buy	1000614-201	6456683	1​	2​
12​	2	Buy	1000614-201	6456688	1​	2​
13​	2	Buy	1000614-201	30-07-131	1​	2​
14​	2	Buy	1000614-201	1033940-001	2​	4​
15​	2	Buy	1000614-201	B0612-2-041	1​	2​
16​	2	Buy	1000614-201	8000632	1​	2​
17​	2	Buy	1000614-201	8000813-006	7​	14​
18​	2	Buy	1000614-201	8000811-001	4​	8​
19​	2	Buy	1000614-201	B0612-2-014	1​	2​
20​	2	Buy	1000614-201	8000767-003	1​	2​
21​	2	Buy	1000614-201	NAS620C5	2​	4​
22​	2	Buy	1000614-201	M12133/5-1P	1​	2​
23​	2	Buy	1000614-201	MS90707-4012	1​	2​
24​	2	Buy	1000614-201	NAS620C5L	2​	4​

I hope that make sense. I am trying to do this as efficiently as possible. (Again in Power Query)

Thank You

 

[JGordon11]

In your example the Unit Quantity is always just two times Quantity. If that's the case then there's not much to it (although I suspect that is not the case).

 

[gheyman]

I must not have explained it well. No its not always x 2. For each row, the "Parent" is identified in the Assembly field. Example MATERIAL item 8000811-001 shows its parent (aka Assembly) as 1000614-201. 1000614-201 has a QUANTITY of 2 (it will not always have a value of 2). In this case , to calculate the the field quantity I need to take the QUANTITY of 8000811-001 and multiply it by the Parent's (1000614-201) Unit Quantity.


What I did, as a temporary fix was to create a duplicate of the table. in that new table I created a column and named it "Parent Quantity" and made it equal to the rows QUANTITY. I then created another column and named it "Parents Level". Here I took the rows LEVEL value and subtracted 1. (Each rows direct Parent will always have a level value of one less - think of it as a Family tree if that helps any)

I then merged the two tables.

= Table.NestedJoin(#"Changed Type1", {"Assembly", "EndItem"}, #"BOM Get Parent's Qty", {"Material", "EndItem"}, "BOM Get Parent's Qty", JoinKind.LeftOuter)

 

[JGordon11]

Change Source in three places to the correct step name.

= Table.AddColumn(Source, "Unit Quantity", each Number.From([Quantity])*Number.From(Source[Quantity]{List.PositionOf(Source[Material],[Assembly])}))

 

[gheyman]

That worked perfectly. Very much appreciated.

Is there a way to find its parent [Make/Buy]? I would love to get rid of the merges.

Thanks again!