Forum Discussion
Union two tables, Sales table with quantity and BOM table
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
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
3 Replies
- AnalyticPulse
Solution Sage
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 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 đ
