Add a new column or merge tables in Power Query in Dataflow in Data Factory

[divyamalhotra12]

I have two tables that I have imported in Power Query DataFlow in Data factory. Table 1 shows forecasts units data for 2 years and table 2 shows shipped data for 6 months.

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?

[divyamalhotra12]

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

[divyamalhotra12]

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.

[miguel]

Can you share the Power Query script that you've created to better unerdstand how things are working today for you?

You can go into the View tab in the ribbon and then select the "Advanced Editor" to show the dialog. Then just copy-paste to here and make sure to remove any personal information from the code that you do not wish to publicly share.

[v-cboorla-msft]

Hi @divyamalhotra12 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[miguel]

any way that you can share the data as a table and not an image? or perhaps a sample file that we could access?

[v-cboorla-msft]

Hi @divyamalhotra12 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks.

[miguel]

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.

[Jonvoge · Accepted solution]

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

[divyamalhotra12]

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