Consolidating 2 Queries in Power Query

[Graemea]

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!

 

[Matt Allington]

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.

 

[Hasnain Johnson]

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.

 

[Graemea]

Thank you both very much!