Merging bill of materials (power query?)

[fvdw]

Hello everyone,

I have been trying to create a program in PowerQuery where i can merge 2 bill of materials based on the equal sub assy descriptions. I have came a long way but could not be able to merge these when the level depthness is more than 1.

I have inserted an example in the appendix of 2 seperate bill of materials and the desired merged bill of material.

Some rules are:

- Bill of material 1 is the main structure, where the bill of material 2 subassy's should be added when there is a match in description (yellow and orange colours).
- The leveling needs to be correct. Should probably at some point be recalculated.
- The green subassy does not have a match so should remain the same.
- The prefered tool to solve this is PowerQuery, however feel free to advice something else

Is this even possible?

Thanks in advance!

[AliGW]

The leveling needs to be correct. Should probably at some point be recalculated.

Without telling us how 'correct' is meant to be calculated, there is no hope of assistance. I cannot work out the logic from your attachment. I think you are going to need to talk us through at least a few of the amendments.

I don't think PQ will be the best option here.

[AliGW]

Just to clarify: a clearly annotated sample workbook will be required in order to clarify the logic that needs to be applied to this task.

[fvdw]

Hi Ali,

Thanks for the quick response,

I will try to elaborate what is correct.

A subassy is an assembly of underlying materials. To recognize a subassy, the row below the subassy has an additional dot. For example:

level 1 is the assembly for level 1.1

level 1.3.4 is the assembly for 1.3.4.1

A subassy can be build through multiple materials. These will have to act like an index. For example:

level 1 is the assembly for level 1.1, 1.2, 1.3 etc.

A subassy can be build by multiple materials and other subassy's. For example:

Level 1 is build thorugh 3 materials being 1.1, 1.2, and 1.3. Additionally, level 1 is build with another deeper subassy which has also 3 materials underlying.
1
1.1
1.2
1.3
1.4
1.4.1
1.4.2
1.4.3


I hope this will give explain what a correct leveling looks like.

If there are any more questions, feel free

[AliGW]

This isn't enough - please annotate the sample workbook and explain EXACTLY how you've made the leveling decisions you've made. This is just theory - you haven't shown how exactly it applies to your sample data.

Sorry for not wanting to spend ages trying to work it out myself, but I don't have time for that, I am afraid. Remember that terms like 'subassy' will mean nothing to most people.

[fvdw]

Hi Ali,

i have added comments to the excel file and reuploaded it to the thread. I hope this is better/enough now? Let me know?

[AliGW]

There are no annotations in the workbook attached to post #1 - did you upload the same file again? Please try uploading the edited version.

[fvdw]

The file is correct, can it be a privacy protection issue?

[AliGW]

No - the forum software is not capable of changing the contents of a workbook. Where are the annotations? This is what you have given us (which is the same as it was to begin with):

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B	C	D	E	F	G
1	Bill of material 1				Bill of material 2
2
3	Level	Description	Type		Level	Description	Type
4	1	A	Assy		1	P	Assy
5	1.1	B	SubAssy		1.1	B	SubAssy
6	1.1.1	C			1.1.1	Q
7	1.1.2	D			1.1.2	R
8	1.1.3	E	SubAssy		1.1.3	S
9	1.1.3.1	F			1.1.4	T
10	1.1.3.2	G			1.1.5	E	SubAssy
11	1.2	H	SubAssy		1.1.5.1	U
12	1.2.1	I			1.1.5.2	V
13	1.3	J	SubAssy		1.2	J	SubAssy
14	1.3.1	K			1.2.1	W
15	1.3.2	L			1.2.2	X
16	1.3.3	M	SubAssy		1.2.3	Y
17	1.3.3.1	N			1.2.4	M	SubAssy
18	1.3.3.2	O			1.2.4.1	Z
19
20	Bill of material merged
21
22	Level	Description	Type
23	1	A	Assy
24	1.1	B	SubAssy
25	1.1.1	C
26	1.1.2	D
27	1.1.3	B	SubAssy
28	1.1.3.1	Q
29	1.1.3.2	R
30	1.1.3.3	S
31	1.1.3.4	T
32	1.1.4	E	SubAssy
33	1.1.4.1	F
34	1.1.4.2	G
35	1.1.4.3	E	SubAssy
36	1.1.4.3.1	U
37	1.1.4.3.2	V
38	1.2	H	SubAssy
39	1.2.1	I
40	1.3	J	SubAssy
41	1.3.1	K
42	1.3.2	L
43	1.3.3	J	SubAssy
44	1.3.3.1	W
45	1.3.3.2	X
46	1.3.3.3	Y
47	1.3.4	M	SubAssy
48	1.3.4.1	N
49	1.3.4.2	O
50	1.3.4.3	M	SubAssy
51	1.3.4.3.1	Z

Sheet: Blad1

[AliGW]

Oh, I think you've added comments to cells - ugh! That's hopeless - sorry. I have to keep hovering over cells to see them.

Add the annotations to the worksheet itself - not as cell comments. You aren't making this easy!

[fvdw]

What about now? Should be visible. Im trying to make it clear

[AliGW]

That's better! Hopefully someone will understand and be able to assist. I have to go out and mow my lawns for the first time this season, so I've run out of time - sorry.