Excel Power Query - Merging And Appending Queries

Power Query Merge and Append

Don`t be put off by the terminology, Power Query is simply sorting and organising data that you wish to import into your Excel spreadsheet and in doing so you can save a lot of time and reduce the scope for errors. The merge and append bit is talking about taking different data sources and adding them together to form a single source from which you can then do all sorts of interesting things!

First of all we need to define what we mean by merge and append. To merge a data set in this case means to add one data set to another by adding additional fields.

For example: If we have a table of data that contains details of employee departments we might want to add it to another data table that contains details of their salaries. In effect we will create a single table with more data fields in the form of additional columns. When merging you need to have what is referred to as a joining data field, this is an identifier that is common to both data tables. In our case it will be the employee number, which should be unique.

In the case of append we are taking one data table and adding additional rows of data to the bottom.

For example: If we have a data table of employees from Germany and we wish to add those from Poland then we can do this by appending one to the other. The important point to note here is that the field or column headers need to be common to both otherwise additional columns will be added to the table.

So why would we use this feature? Imagine you have an organisation where there might be different personnel responsible for inputting or managing the data. In large complex organisations it is common for multiple systems to be in use and for data to come from a range of different sources. Even when using the same systems the data could be kept separate for a variety of reasons legacy or organisational.

This feature allows you to consolidate the data, clean it up and organise it as you wish for further use.

Wan to learn more? Just ask!