Forum Discussion
Add a new column or merge tables in Power Query in Dataflow in Data Factory
Table 1 (Forecasts) has Parts column, unit price and 24 columns showing the 24 months starting from Jan'23 to Dec'24. I unpivoted those columns in Power Query to get one column as 'Month'. Hence, each FCST part now will have 24 rows of data showing the forecasted units every month for 24 months.
In table 2, I have the same Parts Column called Shipped Parts. I grouped the data by part and by month and aggregated by Shipped Qty. It gave me 3 columns. Parts, Month, Qty.
Now, I want to see the parts, month, shipped qty and forecasted qty together in this format.
Upon doing a merge using either left outer, right outer or full outer joins, I am getting 6 rows for each part for each month. I just want to see Part A, Jan, Shipped units and forecasted units in Jan and for other months.
How can I achieve this in Power Query?
Hi divyamalhotra12.
There are a few things which may be causing your issues:
- Are you correctly performing the merge where both Join Columns (Parts and Month) have been highlighted for each of the tables, during the setup? I suspect you might only be joining on the Parts column, hence it exploding your resulting dataset. Left Outer join should work well for you.
- After Merging, you will need to expand the columns of the table you are joining on to the original dataset. If you wish to exclude some columns, you can leave them unchecked during the expand step.
_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data
10 Replies
- Jonvoge
Most Valuable Professional
Hi divyamalhotra12.
There are a few things which may be causing your issues:
- Are you correctly performing the merge where both Join Columns (Parts and Month) have been highlighted for each of the tables, during the setup? I suspect you might only be joining on the Parts column, hence it exploding your resulting dataset. Left Outer join should work well for you.
- After Merging, you will need to expand the columns of the table you are joining on to the original dataset. If you wish to exclude some columns, you can leave them unchecked during the expand step.
_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'.
Find me on LinkedIn, Sessionize, or my blog Downhill Data
The merge worked properly using this solution. There was a problem with unpivit column operation I was performing prior to merging. Once, that got fixed, the merge operation worked properly
- miguel
Community Admin
Hey!
Could you share a screenshot on how you're doing the merge between both tables? I'd definitely use both the Month and the parts columns as the fields to use for the merge. Not just one of them.
I tried doing what you mentioned but it somehow shows me two entries per part.
Is there any other way apart from merge to perform this operation? Can I use the add column feature somehow?
Also, I believe unpivoting the column is not helping at all. For instance, when i click on unpivot column by selecting all 24 columns showing the 24 months then idelally it should show the sum of column 1/1/2023 corresponding the part ID but it does not do that. It is just taking the first value. How can I ensure that I see the sum of the month of jan'23 against the Part ID?
- miguel
Could you perhaps share some sample data of both of your tables and we can work together on creating a sample solution with those?
You can read about the unpivot operation from the link below:
Unpivot columns - Power Query | Microsoft Learn
It doesn't do any sort of aggregation, but you can do the aggregations doing a Group By for example. Below is the link to the documentation around the Group By feature:
How to GROUP BY or summarize rows - Power Query | Microsoft Learn
Forecasted Data - Data goes on from 1/23 to 12/24
Shipments Data - Data goes on from 1/23 to 5/23
I need to see this output in power query in data factory.
