Union two tables, Sales table with quantity and BOM table

[Tendo23]

Hello Folks,

please I need your help again! 😊

I have two tables that need to be merged into a new one.

Table SalesData contains the sales budget for next year.

Product	Quantity	Date	Country
A	2	01.01.2024	USA
A	1	01.02.2024	Belgium
B	3	01.04.2024	China
B	4	01.01.2024	USA
C	1	01.03.2024	France

The second table contains bill of materials for every product:

Sales Product	Raw Material	Quantity
A	Material 1	1
A	Material 2	2
A	Material 3	1
B	Material 1	2
B	Material 4	1
C	Material 2	1
C	Material 3	2

The goal is to bring both tables together.

Product	Quantity Sales Product	Raw Material	Quantity Raw Material	Raw material requirements	Date
A	2	Material 1	1	2	01.01.2024
A	2	Material 2	2	4	01.01.2024
A	2	Material 3	1	2	01.01.2024
A	1	Material 1	1	1	01.02.2024
A	1	Material 2	2	2	01.02.2024
A	1	Material 3	1	1	01.02.2024
B	3	Material 1	2	6	01.04.2024
B	3	Material 4	1	3	01.04.2024
C	1	Material 2	1	1	01.03.2024
C	1	Material 3	2	2	01.03.2024

Column “Raw materials requirements” is calculated column based on “Quantity Sales Product” and “Quantity Raw Material”.

Can anyone help me how to create the new table from the other two?

Tanks a lot!

Tendo

[Tendo23]

Hi @AnalyticPulse ,

Thank you. I have performed the merge in query. I have not seen the wood for the trees... Sometime a small hint hepls... 🙂

[AnalyticPulse]

I'm glad it helped, to keep me motivated, Please subscribe to the Youtube channel @CogniJourney. 
Thanks 😊

[AnalyticPulse · Accepted solution]

I suppose you want to join the tables not uniion of them. 

you can do it in power query. go to merge query and then select your columns which has the common values in your case it seems that you have product column as common column, so use that column to join the tables. i hope you know how to join the tables using merge query.

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney