Aggregating the sum and Unit from a list of Parent item and a corresponding BOM list

[Oleander]

I am doing a budget follow-up and want to sum up all the parts included in our part list A and the corresponding BOM list.

Model1 - Model3 are complete models with parts 1 - 17 included, but in some cases there are some parts with subParts in a BOM and in these cases I want that part as 0 and that I want to add the units from the BOM list instead in the summation.

Any help is appreciated

List A

Model	Part	UM	No
Model1	1	pcs	2
Model1	2	pcs	5
Model1	3	pcs	1
Model1	4	pcs	3
Model1	5	pcs	5
Model1	6	pcs	3
Model1	7	pcs	1
Model1	8	pcs	4
Model1	9	pcs	5
Model1	10	pcs	1
Model1	11	pcs	3
Model1	12	pcs	3
Model1	13	pcs	3
Model1	14	pcs	5
Model1	15	pcs	1
Model1	16	pcs	2
Model1	17	pcs	3
Model2	1	pcs	3
Model2	2	pcs	4
Model2	3	pcs	1
Model2	4	pcs	4
Model2	5	pcs	1
Model2	6	pcs	1
Model2	7	pcs	1
Model2	8	pcs	1
Model2	9	pcs	1
Model2	10	pcs	3
Model2	11	pcs	4
Model2	12	pcs	3
Model2	13	pcs	1
Model2	14	pcs	2
Model2	15	pcs	5
Model2	16	pcs	2
Model2	17	pcs	4

BOM list :

Part	SubPart	UM	No
4	201	kg	1,2
4	202	kg	0,87
4	203	mt	0,3
4	204	mt	0,2
4	205	pcs	1
4	206	pcs	1
5	203	mt	0,3
5	204	mt	0,6
5	205	pcs	4
5	206	pcs	1
6	204	mt	0,8
6	205	pcs	4
6	206	pcs	6

And I want the result of the connected lists to be like this :

Summery Table

Summan av No		Model
Part	UM	Model1	Model2	Total Sum
1	pcs	2,0	3,0	5,0
2	pcs	5,0	4,0	9,0
3	pcs	1,0	1,0	2,0
4	pcs	0,0	0,0	0,0
5	pcs	0,0	0,0	0,0
6	pcs	0,0	0,0	0,0
7	pcs	1,0	1,0	2,0
8	pcs	4,0	1,0	5,0
9	pcs	5,0	1,0	6,0
10	pcs	1,0	3,0	4,0
11	pcs	3,0	4,0	7,0
12	pcs	3,0	3,0	6,0
13	pcs	3,0	1,0	4,0
14	pcs	5,0	2,0	7,0
15	pcs	1,0	5,0	6,0
16	pcs	2,0	2,0	4,0
17	pcs	3,0	4,0	7,0
201	kg	3,6	4,8	8,4
202	kg	2,6	3,5	6,1
203	mt	2,4	1,5	3,9
204	mt	6,0	2,2	8,2
205	pcs	35,0	12,0	47,0
206	pcs	26,0	11,0	37,0

[Oleander]

Hi,

I have a foloowup question about this BOM Calculation :

I have now a list i Power Pivot that I want to check if I'm missing any pricing from the Price list that is a Lookup lsit to this BOM;
How should I go about to get the "X" in the column "Cost Driven" , as mandetory pricecheck from the Price-lookup database ?

It's only the child that is allowed to have a price.

Path	Parent	Comp	Amount	Unit	Cost Driven
1	1	1		EA
1|123	1	123	1	EA
1|123|1245	1	1245	1	EA	X
1|123|8005	1	8005	1,2	KG	X
1|31363707	1	31363707	1	EA
1|31363707|8005	1	8005	0,3	KG	X
1|31477900	1	31477900	8	EA	X
1|318326	1	318326	1	EA
1|318326|15678	1	15678	1	EA	X
1|318326|51688111	1	51688111	1	EA
1|318326|51688111|61363688	1	61363688	1	EA	X
1|318326|51688111|00239	1	239	1,06	KG	X
1|318326|318149	1	318149	1	EA
1|318326|318149|61363702	1	61363702	1	EA
1|318326|318149|61363702|75363975	1	75363975	1	EA	X
1|318326|318149|61363702|91348911	1	91348911	6	EA	X
1|318326|318149|61363702|500223	1	500223	1,4	KG	X
1|318326|318149|61363702|500224	1	500224	2,6	KG	X
1|318326|318149|88688537	1	88688537	1	EA	X
1|318326|318149|50395825	1	50395825	0,4	KG	X
1|318326|318149|50424739	1	50424739	0,8	KG	X
1|119115	1	119115	4	EA	X

[dufoq3]

Hi @Oleander, you can achieve this in Power Query.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZMxcsQgDEWvknFNISEJiTJFJkWOsLM34fDRZkHGTMimwNaD/2Uh8O124JFivPn4eD/uyacbZhpLEc2LPlhCEeFFYgBjfoQp+/Prs4sIqZCCdtGCkWvM/5IQEl0TsmoFiISBdk1olEuIJlgkDaWojW1O8aoTLGaIo5UL7tStPHbmcdctuLUBZKpd1KME5dqIH4e/kOu50RPW3M/FZ0EKeS7oxBempuJB1XFGC75yVyS2Gk2bsPzDLd6UuKknJN62ZfHy7H1ATvuWNjM/C6FxVxfcFCtAVSxLfCkQ/qpTgDNrnPiEkGy2IXq/4q8cwL2g+zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Path = _t, Parent = _t, Comp = _t, Amount = _t, Unit = _t]),
    Paths = List.Buffer(Source[Path]),
    Ad_CostDriven = Table.AddColumn(Source, "Cost Driven", each if List.Contains(Paths, [Path] & "|", (x,y)=> Text.StartsWith(x,y)) then null else "X", type text)
in
    Ad_CostDriven

[Oleander]

I wonder if I could use this formula without getting dublicates if the formula is picking up the component number in the Parent column for another Parent part ?

Check=

VAR _SEL = SELECTCOLUMNS(BOM;"NP";PATHITEMREVERSE(BOM[Path];2))

Return

IF(BOM[Comp]<>PATHITEM(BOM[Path];1);IF(BOM[Comp] IN _SEL;BLANK();"X");BLANK())

Appreciate any help from the community

//Göran

[Oleander]

HI Stéphane,

Thank you for you support. It worked and I'm so grateful for your solution.

//Göran

[slorin · Accepted solution]

Hi,

let
Source = Table.NestedJoin(Table_A, {"Part"}, Table_BOM, {"Part"}, "Table_BOM", JoinKind.Inner),
Expand = Table.ExpandTableColumn(Source, "Table_BOM", {"SubPart", "UM", "No"}, {"SubPart", "UM.BOM", "No.BOM"}),
#"Replaced Value" = Table.ReplaceValue(Expand,each [No],0,Replacer.ReplaceValue,{"No"}),
#"Removed Other Columns" = Table.SelectColumns(#"Replaced Value",{"Model", "Part", "UM", "No"}),
#"Inserted Multiplication" = Table.AddColumn(Expand, "No.BOM2", each [No.BOM] * [No], type number),
#"Removed Other Columns1" = Table.SelectColumns(#"Inserted Multiplication",{"Model", "SubPart", "UM.BOM", "No.BOM2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"UM.BOM", "UM"}, {"No.BOM2", "No"}, {"SubPart", "Part"}}),
Custom1 = Table.NestedJoin(Table_A, {"Part"}, Table_BOM, {"Part"}, "Table_BOM", JoinKind.LeftAnti),
#"Removed Columns1" = Table.RemoveColumns(Custom1,{"Table_BOM"}),
Custom2 = Table.Combine({ #"Removed Columns1", #"Removed Other Columns", #"Renamed Columns"}),
#"Pivoted Column" = Table.Pivot(Custom2, List.Distinct(Custom2[Model]), "Model", "No", List.Sum)
in
#"Pivoted Column"

Stéphane