Lookup Multiples from Exploded BOM Lines and return units used

[C Goody]

Hi there I wonder if someone can help, Im a bit of a novice and learning my way, I have an exploded BOM from assembled products direct from a data source with various components and quantities that make up the assembled product. I am looking for a way that I can look up from a long list of product sales data of assemble units what components were used and how many over a timeline say each month so I can forecast stock requirements

The source data has assembled product codes and the qty sold

The BOM looks like this

Bom Assembley	BOM Component	BOM Description	BOM component Qty
BOM1	ABC1	ABC1 DESCRIPTION	4​
BOM1	XYZ1	XYZ1 DESCRIPTION	4​
BOM2	ABC1	ABC1 DESCRIPTION	4​
BOM2	DEF1	DEF1 DESCRIPTION	4​
BOM3	ABC1	ABC1 DESCRIPTION	4​
BOM3	ZYX1	ZYX1 DESCRIPTION	4​
BOM4	FEG1	FEG1 DESCRIPTION	8​
BOM4	ABC1	ABC1 DESCRIPTION	4​
BOM4	XYZ1	XYZ1 DESCRIPTION	2​
BOM5	FEG1	FEG1 DESCRIPTION	8​
BOM5	ABC1	ABC1 DESCRIPTION	4​
BOM5	DEF1	DEF1 DESCRIPTION	2​
BOM6	FEG1	FEG1 DESCRIPTION	8​
BOM6	ABC1	ABC1 DESCRIPTION	4​
BOM6	ZYX1	ZYX1 DESCRIPTION	2​
BOM7	ABC1	ABC1 DESCRIPTION	4​
BOM7	XYZ1	XYZ1 DESCRIPTION	4​

The units sold are like this

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

BOM1

13​

45​

12​

13​

14​

25​

14​

48​

32​

10​

8​

5​

BOM2

12​

54​

26​

35​

23​

12​

11​

45​

69​

21​

26​

21​

BOM3

11​

10​

12​

16​

15​

13​

18​

19​

44​

12​

23​

22​

BOM4

43​

56​

63​

65​

54​

24​

58​

59​

65​

45​

32​

43​

BOM5

10​

10​

12​

14​

51​

32​

14​

26​

35​

8​

12​

45​

BOM6

65​

78​

59​

45​

84​

65​

98​

44​

75​

25​

48​

45​

BOM7

64​

62​

65​

61​

63​

65​

64​

66​

68​

69​

67​

66​

How do I look up how many components were used where same components are used for multiple BOMS, either in excel or through power query

Components Used

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

ABC1

XYZ1

ABC1

DEF1

ZYX1

FEG1

Hope that makes sense and thank you