Appending tables in Excel using Power Query is a quick way to combine datasets by stacking them on top of each other. An append will take two tables, combine them vertically and automatically align the columns for you. It’s the same action as a SQL Union but using the user-friendly interface of Excel.
There are countless use cases for appending data in Excel using Power Query. Some of the most common are combining reports that have the same information but over different time periods or categories. We’ll explain how to perform an append, and also go over some best practices and ways to consolidate folders full of Excel files to compliment the Excel append process.
Let’s dive in!
Table of Contents
When to Use Append in Power Query
Appending data in Power Query should be used when you need to combine rows from two or more tables with the same columns. It’s the perfect solution when you have data spread across multiple tables, spreadsheets, databases or combination of the three and want to work with one unified dataset.
In general, use a Power Query Append in Excel when:
- You’re dealing with similar datasets split over time or categories.
- You need to perform the same analysis on multiple datasets.
- You want to consolidate data for better visualization, reporting, and pivot tables.
By appending data in Excel, you create a continuous view of your information. By using Excel Power Query you can perform the action once, and press refresh when the data sources update saving you time if data changes in teh future.
Power Query Append is Similar to a SQL Union
In essence, the Append feature in Power Query is similar to the SQL UNION operation. SQL is the query language used in most databases. Both a union and an Excel Append stack datasets on top of each other, provided they have the same number of columns and corresponding data types. The goal is to create a single dataset from multiple sources without duplicating columns.
However, unlike SQL UNION, which strictly requires the same data types in each column, Power Query can be more forgiving, allowing some level of type coercion to append datasets with slightly different data types.
Managing Data Types when Appending in Power Query
Ensuring consistent data types across all columns is critical when appending data in Power Query. Inconsistencies can lead to errors or unexpected results. Power Query tries to automatically detect and match the data types, but it’s always best to manually verify and adjust them if necessary before appending.
To manage data types effectively:
- Check each column’s data type in all tables before appending.
- Right-click on column headers in Power Query and select a new data type for each column.
- Be aware that text data can often absorb other types, but this may not be ideal for calculations or sorting.
By carefully managing data types, you will avoid some of the most common errors when appending data in Excel Power Query.
Pre-Requisite for Appending Data in Power Query
Before you can append data in Excel Power Query, the data tables have to be loaded into Power Query. Meaning that you will have to use the Get & Transform function to Get Data and select either the table in Excel or connect to a data source.
The data tables that are already loaded into Power Query, also known as Queries will appear on the left side of Power Query.
Power Query can only append and work with data that has been imported into Power Query. You will not be able to append a regular spreadsheet with a Power Query unless you first import the regular spreadsheet into Power Query.
How to Append in Power Query
To append data in Excel Power Query, launch the Power Query Editor, select the table you want to append with others. Then select Append Queries from the Home Tab of Power Query. A dialog box will appear where you can choose the tables you want to perform the load on.
To append tables in Power Query, follow these steps:
- Open the Power Query Editor.
- Select the table you want to append to others.
- Go to the Home tab and click on Append Queries.
- In the Append window, select the tables you wish to append from the available list.
- Click ‘OK’ to perform the append operation.
Here’s how in more detail.
Select a Dataset and Click on Append Queries
The Append Queries button is available towards the middle of the Home Ribbon in the Power Query Editor. When you click on it you will see a dropdown menu. There are two options, Append Queries or Append Queries as New.
Choosing Append Queries will combine the selected datasets with the dataset that is selected. Choosing Append Queries as New will result in a new dataset being created.
While a regular append combines additional data with the first dataset selected, choosing append queries as new will result in an entirely new dataset, and will look like the following:
Append as New is probably less common but can come in handy when you want to ensure that you have clean copies of the original datasets or need to work with them individually later for other purposes.
Choosing a Single Table to Append
The default setting when appending is to append a single table to the table that is initially selected. The appended table will follow the column layout of the original table. Click the dropdown menu to choose a specific table to append, and press OK.
Choosing Multiple Tables to Append
To append more than one table together in Excel Power Query, change the option at the top of the append dialog box from Two Tables to Three or More Tables. A screen will appear that lists all available tables that have been loaded into Power Query on the left side of the screen. Select a table, or multi-select tables and click Add. This will add it to a list of tables to append to the first table.
When you press OK, all of the tables in the right column, Tables to Append will be consolidated into a single tbale.
Understanding the Order Files are Appended in
The order in which files are appended in Power Query depends on how they are selected during the append process. If appending using the user interface, the files are appended in the order they are selected. When using advanced editor or M code, the order is determined by the sequence of the queries listed in the code.
Appending Data in Excel Power Query with Different Data Types
A common problem when appending data in Power Query occurs when there are different data types between datasets being combined. At best it can be annoying to see data that’s not consolidating the way you want it to, at worst it can cause errors in subsequent queries or cause incorrect analysis of the data later on.
The example below shows two different datasets that have been appended but have different data types. As you can see the Transaction ID column doesn’t align numbers because it is a mix of both text and numeric data.
To fix this, you can either correct the data type by right clicking on a column header to change it prior to appending the datasets together, or you can change the data type of each column after an append to ensure consistency, assuming no transformation errors occur.
Working with Data with Different Column Headers
Handling data with different column headers in Power Query for Excel requires you to rename columns prior to appending. To do this, you should rename column headers in your tables to match exactly, as Power Query identifies columns to append based on header names.
When columns have different names, Power Query will align what it can, using the first selected table as a template, but anything columns that don’t have a match will be added to the right side of the resulting table. The column that you would expect to have been lined up will have a bunch of null values signifying that Excel doesn’t know what should go there.
To fix this issue, or to avoid it completely:
- Use the Rename Columns feature in Power Query to standardize column names by double clicking into a column header and typing a new name.
- Ensure all columns that are meant to be appended together share the same header name across all tables.
It’s important to check your full dataset. It’s pretty common to have 90% of data combine then have one file or table that’s slightly off causing an issue that may not be visible until you go to analyze your loaded data.
Power Query has several data profiling tools that can help you identify null values within columns to avoid a manual review process.
Combining Multiple Files in Power Query vs Appending Tables
Combining multiple files in Power Query and appending tables are two different operations. Combining files typically refers to merging files from a folder, automatically detecting and combining like-named columns from each file. Appending tables is more about manually selecting specific tables to stack together.
Combining files is ideal when you have a folder with similarly structured files, and you want to create a single dataset from them. Power Query will use the column names to automatically align and combine the data.
It’s similar to an append, but it’s faster than having to import each table individually into Excel.
To import and consolidate a folder full of Excel or CSV files, go to Get Data, From File, From Folder on the Data Tab of Excel.
Excel will automatically bring the data into Power Query, and you can apply data transformation steps on the full dataset. If you later add a file to the folder, or delete one Excel Power Query will automatically adjust and show all of the information that exists in the folder.
This technique is exceptionally useful for accounting and finance use cases or any time you have a monthly report that has to be added to a trend report.
Appending queries in Excel Power Query is similar to performing a union in SQL. Power Query takes a selected table and uses it as a template to append or stack additional data below it from different tables. For an append to work as expected, it’s best practice to make sure that all of the columns of the incoming data have the same column titles and data types assigned. Otherwise, you can run into errors or mis-aligned columns.
Power Query gives users the ability to append to an existing table, or append to create a new table. You also have the ability to append one or multiple tables together.
In use cases where you have many different files to consolidate, it can be quicker to get data from a folder than it is to import each individual file into Power Query to be appended.