Excel Power query merging different workbooks

0
votes

I can't find the solution for the problem described here.

I have an Excel file with sales data of 2020 and another one with data for 2021, with lots of rows, so if I copy paste one below the other in the other Excel, I can't use pivot data because too many rows, so I want to merge my 2 Excel files in this way:

First table:

First Table

Second table:

Second Table

Desired final table (in Excel):

enter image description here

Is there any way I can do that with power query or something else in Excel?

Note: my table doesn't have just Sales 2020 in 2021, but also other data, but for simplicity I didn't include it there (example: growth 2020, growth 2021)

So if anyone can help me I will appreciate it a lot!

1
excelpivotpowerquery
Load the tables into powerquery. Merge one table into the other by matching the first three column, with a sum operation on the sales column, using a left outer join. Expand the resulting column for the missing year using the arrows atop the new column – horseyride

1 Answers

0
votes

I would start with 2 queries, each one would just read the rows from First table and Second table respectively.

Then I would start a new query by Reference to the First query.

In this Output query I would add a Merge Queries step, matching the first 3 columns from the First and Second queries. I would set the Join Type set to Full Outer Join.

Next in the Expand step I would return all the columns from the Second table.

Finally I would add 3 columns using the Conditional Column option, to create merged versions of Name, Surname and Month. For example:

= if [Name] = null then [Second.Name] else [Name]

Finally I would remove the un-needed columns, rename and re-order the columns if needed.