Introduction
In our last lesson, you mastered the Merge operation in Power Query, learning how to combine tables horizontally by adding new columns based on a shared key. This is the equivalent of a lookup. Today, we'll explore the other fundamental way of combining data: Appending.
This lesson focuses on stacking tables vertically to consolidate rows. Imagine you have separate monthly sales reports, each in its own table or file. Appending allows you to combine them into a single, comprehensive master list for year-to-date analysis. This is a cornerstone skill for any data professional, enabling you to efficiently consolidate fragmented data from multiple sources.
By the end of this lesson, you will be able to confidently use Power Query's Append feature to combine rows from multiple tables, handling both simple and complex real-world scenarios.
1. The Core Concept: Stacking Data with Append
Appending is the process of taking two or more tables that share a similar structure and stacking them on top of one another to create one longer table. While merging adds columns, appending adds rows.
The image below shows a simple example: two tables, one for March sales and one for April sales, are appended to create a single combined table.
For this to work correctly, there are two critical prerequisites.
How to Combine Tables with Power Query
This article from Excel Campus clearly lays out the two essential rules for appending data.
Please read the introduction and the two prerequisites, from the beginning of the article down to the prerequisites section. Pay close attention to the requirement for consistent column headings.
As the article states, Power Query uses column headers to decide which columns to stack on top of each other. If the headers don't match (e.g., "Product" in one table and "Item" in another), Power Query will create two separate columns in the final table, which is usually not the desired outcome.
2. The Manual Append Method
The most direct way to append is to load each table into Power Query as a connection and then use the Append Queries command. This gives you full control and is a great way to understand the underlying process.
Power Query Append / Combine Tables: 3 Amazing Methods. Excel Magic Trick #1714.
This video from the excelisfun channel provides an excellent walkthrough of three different appending techniques. We'll start with the first, most fundamental method.
Watch the segment from the beginning until the end of the manual method. The presenter demonstrates: Loading individual Excel tables into Power Query as "connection only" queries. This is a best practice as it doesn't load intermediate data into your workbook. Using the Append Queries as New command to combine the connection queries. Editing the appended query later to add a third table.
After appending, the Power Query Editor will show a new, combined query. The Queries pane on the left will list your original source queries as well as the new appended query, which is typically named Append1 by default.
3. Handling Mismatched Column Headers
In a perfect world, all your data sources would have identical column names. In reality, especially in freelance work, you'll often get files with slight variations, like "Customer" vs. "Client" or "Sale_Amount" vs. "Value". As we've learned, this will cause problems.
Power Query provides a straightforward way to fix this. The key is to standardize the column names in each source query before you append them.
Power Query Append: Quickly combine many queries into 1
This article from Excel Off The Grid walks through this exact problem and presents the solution.
Please read the section on handling different headers. The author demonstrates what happens when a column name is different ("Customer" vs. "Sold To") and how it results in two separate columns after appending. The solution is to simply rename the column in one of the source queries to match the other. This is a vital troubleshooting skill.
This principle is crucial: Power Query appends based on column names, not column position. Always ensure your column names are consistent across all queries you intend to append.
4. Automated Append Methods for Efficiency
Manually connecting to every single table works, but it can be tedious if you have dozens of tables or files. Power Query offers more powerful, automated methods for these common scenarios.
Method 1: Combining All Tables in the Current Workbook
If all your data is split across multiple Excel Tables within the same workbook, you can use a special function to grab them all at once.
Power Query Append / Combine Tables: 3 Amazing Methods. Excel Magic Trick #1714.
Let's return to the excelisfun video to see the second method.
Watch the section explaining the Excel.CurrentWorkbook function from this segment. This is a more advanced technique, but incredibly powerful. Focus on two key steps: He uses the formula =Excel.CurrentWorkbook() to automatically pull a list of all tables in the file. Crucially, he adds a filter step to exclude the final output table itself. This prevents the query from re-importing its own results and creating duplicate data every time it refreshes.
Method 2: Combining All Files from a Folder
Perhaps the most common scenario for a data freelancer is receiving data as a collection of separate files (e.g., a folder containing a CSV or Excel file for each month). The "From Folder" connector is designed specifically for this.
This method creates two important items in Power Query:
- A Sample File query, which defines the transformation steps to be applied to every single file.
- A Final Query, which consolidates the results from all files after the sample transformations have been applied.
This distinction is critical. If your source files are messy (e.g., they have extra header rows or need columns unpivoted), you must perform those cleaning steps in the Sample File query. This ensures each file is cleaned before they are all appended together.
Combine Files from a Folder with Power Query the RIGHT WAY!
This video from MyOnlineTrainingHub provides an expert-level explanation of the "From Folder" method, emphasizing best practices for dealing with messy data.
Please watch from the main demonstration. Pay close attention to these parts: Connecting and Combining: The initial steps of connecting to the folder and launching the Combine Files dialog (01:01 - 03:04). The Critical Concept: The explanation of why some transformations must be done in the Sample File query (03:04 - 03:50). Practical Example: A brilliant demonstration of cleaning messy, multi-row headers in the Sample File query so the final appended data is clean (03:50 - 07:49).
Mastering the "From Folder" workflow, especially the concept of transforming the sample file, will save you countless hours on client projects and is a hallmark of a proficient data analyst.
Conclusion
In this lesson, you've learned how to consolidate data by appending tables. You've moved from the basic manual method to powerful, automated techniques that can handle real-world data challenges. This, combined with your knowledge of merging from the previous lesson, gives you a complete toolkit for combining data in Power Query.
Key Takeaways:
- Append adds rows: Use it to stack similarly structured tables vertically into one master list.
- Consistent headers are mandatory: Power Query relies on matching column names to append data correctly. Rename columns in your source queries as needed before appending.
- Use the right tool for the job:
- Manual Append: Good for a few tables and for understanding the process.
Excel.CurrentWorkbook(): Best for combining many tables within a single Excel file.- From Folder: The standard for consolidating data from multiple external files (CSVs, Excel workbooks, etc.).
- Clean in the Sample File: When using the "From Folder" method, perform any necessary cleaning and shaping transformations on the "Transform Sample File" query to ensure all files are processed correctly before being combined.
In our next lesson, we will tackle a different kind of data shaping. We've learned to combine tables, but now we'll learn how to transform data that is in a "wide" or "crosstab" format into a "tall," tidy format using the Unpivot feature. This is often a necessary step before you can effectively analyze or visualize your data.