The Difference Between Power Query Merge and Append

Power Query Merge and Append are two functions in Power Query used to combine multiple tables or files together. However, they serve two very different purposes. We’ll explain the difference and show what it looks like when datasets are merged or appended.

Append and Merge are two popular methods to combine tables in Power Query, we'll explain the difference between them

Deciding whether to merge or append will largely depend on your dataset and what your end goal is for your data. Appending data is similar to copying and pasting two spreadsheets together to get one consolidated table of data, while Merge is more similar to a VLOOKUP but not quite the same.

Let us explain!

The Difference Between Power Query and Excel

Before explaining what Merge and Append do in Power Query, it helps to level set by explaining how and why working in Power Query is different from working in spreadsheets and regular Excel.

Rather than creating a spreadsheet cell by cell with formulas, Power Query works by importing files and tables of data. Each table represents a query. Once imported, data transformations and calculations are applied at the table or column level.

Some of the implications mean that you can’t write a formulas like you would in Excel, such as VLOOKUP to bring in columns from another data table, and you can’t copy and paste different tables or files together to get one big table. Data must be imported into Power Query and then Merged or Appended using the Power Query editor which will combine columns and rows from different datasets.

When to Combine Tables with Power Query

Combining tables is essential when you have data spread across multiple sources but need to analyze it as a single dataset. If your end goal is to analyze data in Power BI or a Pivot Table, it has to be one continuous table of rows and columns.

This situation is pretty common in scenarios like consolidating monthly reports, combining data from different departments, matching up sales figures with customer information, or integrating datasets from various systems.

Power Query lets you combine data using two primary methods, append and merge.

What Append Does in Power Query

Appending is the process of stacking one table on top of another, creating a single, long table. This method is ideal when your tables have the same structure, with matching columns, and you want to consolidate data into a single larger dataset.

The example below shows two separate Excel tables, the one on the left has February sales figures and the one on the right has March sales. The column headers and number of columns are identical which isn’t strictly a requirement but makes your data line up more nicely.

Example of two separate tables with similar data from different months of the year

After both tables are imported into the Power Query Editor they show up on the left side of the screen as separate Queries with 9 rows each.

Select Append Queries from the Home section of the Power Query Ribbon, and Merge in Place or as New.

Example of both tables imported into Power Query to Append Queries

Once appended, the data is combined into one long table of 19 rows. Power Query automatically aligns the columns based on column name. The first table that’s selected becomes the template for the remaining tables that are appended.

The resulting long table from appending two queries

Power Query provides options for appending two tables together or multiple tables at the same time. There are also options to import folders full of Excel files so they automatically append.

Removing Duplicates after Appending Tables

After appending tables, duplicates might emerge, especially if the combined tables had overlapping data. Power Query provides tools to easily identify and remove these duplicates. Right-click on a column header to view the Power Query Quick Actions and choose Remove Duplicates.

Removing duplicate values after appending tables in Power Query

This method ensures that data isn’t duplicated if datasets have overlapping information after being appended.

What Merge Does in Power Query

Merging is akin to performing a database join or using a VLOOKUP in Excel. It comes two tables based on a common column or key. Merge is particularly useful when you have related data in different tables that you need to bring together for analysis. Power Query supports various types of merges, including inner joins, outer joins, and cross joins, each serving different data relationship needs.

In the example below, we have two tables. One for February Sales, and another that has an Item Number and the corresponding product name. The common column is highlighted in yellow.

Example of two different tables with common columns of values to merge together

After importing both tables into Power Query, select the master table or the primary dataset. This will become the left side of the merge.

Click on Merge Queries from the Home section of the Power Query Ribbon to launch the Merge Editor.

Two tables about to be merged together in Power Query using the Merge Queries button

With the Merge Editor open, you can select the second dataset you want to merge with the primary one. Then click on the columns that the datasets have in common. When selected they will highlight green.

The Merge Queries editor with common columns selected and a Left Outer Join Kind

When you press OK, the editor will close and you’ll see a new column that is full of the word Table on the right side of the initial dataset.

Screenshot of joined tables with the newly added columns collapsed

Click the left/right arrow at the top of the new column on the right side of the header. A dialog box lets you select the columns you want to expand. When you press OK again, the new column will appear on the right side of the original data based on the common value in the columns selected.

Expanding a collapsed column after joining two datasets together to recreate a VLOOKUP

As you can see, we essentially re-created the functionality of an Excel VLOOKUP using Power Query with a Merge, but the process has a few distinctions that you should be aware of which will make working with it easier.

The Difference Between Merge and Excel VLOOKUP

There are a number of differences between Merge Queries and VLOOKUP.

  • VLOOKUP works on one column at a time, Merge can bring in multiple
  • VLOOKUP only works left to right, Merge has no column order preference
  • VLOOKUP will always perform a Left Outer Join, Merge enables Join Types
  • VLOOKUP can be performed for a cell, or part of a column, Merge applies to all rows in a column.

Understanding Different Merge Types

Power Query offers several merge types, mirroring the functionality of SQL joins. The functional difference is that you’re telling Power Query what you want it to do with leftover data when not everything matches. For example, you could be comparing a bank statement with vendor payments. It’s unlikely that all transactions will match 1 to 1 so the Merge type dictates whether you see only matches, items that didn’t match one document or the other, or if you get to see everything regardless of the matching status.

  • Inner join combines rows that have matching values in both tables
  • Outer join includes all rows from one table and the matched rows from the other
  • Full Outer includes everything in the results and matches up what it can.
  • Anti Joins show the data that didn’t match from one table or the other.

Join Types are managed in the Merge editor and can be selected from a dropdown menu at the bottom.

Selecting different Join Types in Power Query merge to provide different results

There are also scenarios where you might duplicate or create reference tables that let you apply multiple merge types in your data. This method can create multiple resulting tables to see which items matched, and which ones didn’t by applying different join types.

Conclusion

Power Query is a robust tool that simplifies data manipulation, offering versatile options to combine datasets through appending and merging. By understanding how and when to use these functions, along with the nuances of different merge types and the importance of removing duplicates, you can enhance your data analysis efforts. Power Query’s intuitive interface and powerful capabilities make it an essential tool for business users looking to harness the full potential of their data.

Scroll to Top