Consolidating 2 Queries in Power Query

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have two queries in Power Query, each with a list of product codes in the first column and sales values in the second column.

I would like to merge these queries to create a new query that has a single instance of each product code, taken from the two queries mentioned above, in the first column and the sales values from the first table in the second column and the sales values from the second table in the third column of the new merged query.

I know the mechanics of merging the queries in PQ but the problem I have is that while most of the product codes are common to both queries, there are a few in each table that are not in the other.
Can someone please tell me how to set up the merged query so that a single instance of all product codes in both tables are included, together with their associated sales values as described above?

Thanks!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I suggest
1. load both queries and set them so they don’t load
2. Add a third column to each query called “source” adding the file name in this column
3. Creat a new query that appends both tables
4. Pivot the source column, aggregating the numeric column

that should do it.
 
Upvote 0
Solution
Select two or more columns that you need to merge. To select more than one column contiguously or discontiguously, press Shift+Click or CTRL+Click on each subsequent column. The order of selection sets the order of the merged values. Select Transform > Merge Columns.
 
Upvote 0

Forum statistics

Threads
1,223,406
Messages
6,171,927
Members
452,434
Latest member
NUC_N_FUTS2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top