Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 21

Merging Datasets from Multiple Sources

Recommended resources

Hello! In our last lesson, we focused on merging data, which is about combining tables horizontally to add new columns of information. Now, we will explore the other fundamental way to combine data: appending.

This lesson addresses how to append data from multiple sources into a unified dataset. Where merging is like adding columns, appending is like stacking rows. For the managers in your workshop, this is a game-changer for consolidating periodic reports (like monthly sales files) or data from different regions into a single, analysis-ready table. This eliminates tedious and error-prone copy-pasting.

By the end of this lesson, you will be prepared to teach managers how to:

  • Distinguish between appending (stacking rows) and merging (adding columns).
  • Append multiple tables from within a single Excel workbook.
  • Append all files from a folder into a single table, a powerful automation technique.
  • Understand the difference between Append Queries and Append Queries as New.

From Merging to Appending: What's the Difference?

Let's start by reinforcing the distinction from our last lesson.

  • Merge: Combines tables with different but related data by adding columns. You need a common key column to link them. (e.g., adding Product Name from a Products table to a Sales table).
  • Append: Combines tables with the same structure by stacking them on top of each other, adding more rows. (e.g., stacking January Sales, February Sales, and March Sales into a single Q1 Sales table).
Appending Tables in Power Query
This image illustrates the core idea of appending. Two tables with the same columns ('Sales March' and 'Sales April') are stacked vertically to create a single, longer 'Combined Table'.

The key prerequisite for a successful append is that the columns you want to combine must have identical headers. The order of the columns doesn't matter, but the names do. Power Query matches columns by their header name.

The Two Ways to Append: Append Queries vs. Append Queries as New

Power Query gives you two ways to perform this operation, found under the Append Queries button on the Home tab:

  1. Append Queries: This modifies your currently selected query by adding the rows from another query to it.
  2. Append Queries as New: This is the recommended approach for your workshop. It leaves the original source queries untouched and creates a brand-new query containing the combined data. This is a cleaner, non-destructive workflow that is much easier for beginners to understand and troubleshoot.

To learn the fundamentals of this feature, the official Microsoft documentation provides a clear and concise overview.

Append queries - Power Query

This article from Microsoft Learn, 'Append queries', explains the core concepts clearly.

Read the sections 'Append queries', 'Append two tables', and 'Append three or more tables'. Focus on understanding the difference between the inline 'Append queries' and the safer 'Append queries as new' option. Note how Power Query handles columns that don't match perfectly.

Scenario 1: Appending Multiple Tables within a Single Workbook

A common scenario for managers is receiving an Excel file with data split across multiple worksheets—for example, sales data for different reps or regions, each on its own sheet. The goal is to combine them all into one master list.

The key steps for your workshop participants will be:

  1. Ensure the data on each sheet is formatted as a proper Excel Table with a unique name.
  2. Create a "connection-only" query for each table. This loads the data into Power Query's memory without creating a new sheet in Excel for each one.
  3. Use Append Queries as New to select and stack all the connection-only queries into a single, unified table.
  4. Load the final, appended table back into Excel or Power BI.

This video provides an excellent walkthrough of this exact process.

How To Combine Excel Tables And Worksheets With Power Query

This video, 'How To Combine Excel Tables And Worksheets With Power Query' from Excel Campus, demonstrates how to stack multiple Excel tables from different sheets.

Watch from 00:33 to 07:07. Pay close attention to these key teaching points: Prerequisites (00:33 - 01:57): The presenter emphasizes that data must be in named Excel Tables with consistent headers. Creating Connections (01:57 - 03:47): Notice how he uses 'Close & Load To...' and selects 'Only Create Connection'. This is a crucial step to avoid cluttering the workbook. Appending (04:35 - 06:03): He uses 'Append Queries as New' and selects 'Three or more tables' to combine all the connections at once. Loading the Result (06:32 - 07:07): The final combined query is then loaded as a new table.

Scenario 2: Appending All Files from a Folder

This is perhaps the most powerful and time-saving technique you can teach your managers. Imagine they receive a monthly sales report as an Excel or CSV file from various departments. Instead of opening and copying each one manually, Power Query can connect to the folder and automatically combine all the files. When a new file is added to the folder next month, they just need to click "Refresh."

The process is slightly different but incredibly efficient:

  1. Use Get Data > From File > From Folder.
  2. In the preview window, choose Combine & Transform Data.
  3. Power Query will ask you to select a sample file and a sheet/table within it to use as a template.
  4. Power Query automatically generates a set of steps to transform each file according to the sample, and then appends them all together.

This video from Leila Gharani is a fantastic resource for teaching this workflow.

Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)

Watch 'Easiest way to COMBINE Multiple Excel Files into ONE' to see the folder combination method in action. This is a workflow that will impress your audience.

Watch from 01:28 to 07:13. Focus on these concepts to explain them in your workshop: Connecting to the Folder (01:28 - 02:06): The starting point of the workflow. Filtering Files (02:06 - 02:40): A useful step to ensure only the correct files are included (e.g., filtering out temporary files). Combining and the Sample File (02:40 - 03:24): Understand that Power Query uses one file as a 'blueprint' for all others. Cleaning the Sample File (04:35 - 07:13): This is a critical point. Any cleaning (like removing top rows or blank rows) should be done on the sample file query. Power Query will then apply those same cleaning steps to every file before appending them, ensuring consistency.

As the video highlights, this method is robust to the order of columns in the files but is sensitive to the column header names. A Sales Value column will not match a sales value column. This is a key point to emphasize about data consistency.

Test your understanding!

A manager receives weekly inventory reports as separate CSV files in a shared folder. The file names are Week1_Inventory.csv, Week2_Inventory.csv, etc. Each file has the exact same columns: ProductID, ProductName, StockLevel, Warehouse.

The manager wants to create a single Power BI report that tracks total inventory over time. Which of the two scenarios we've discussed is the best fit for this task, and what are the main steps you would instruct them to follow in Power Query?

Show answer

Scenario 2: Appending All Files from a Folder is the perfect solution. It's automated and scalable.

The main steps to teach the manager are:

  1. In Power BI Desktop, select Get Data > More... > Folder and connect to the shared folder containing the CSV files.
  2. In the preview window, select Combine & Transform Data.
  3. Power Query will show a preview of the combination. Since CSV files don't have sheet names, this step is simpler than with Excel. Click OK.
  4. Power Query will automatically create the query that appends all files.
  5. Check the applied steps and data types to ensure everything was imported correctly.
  6. Click Close & Apply to load the single, unified table into Power BI for analysis.
  7. Emphasize that when the Week3_Inventory.csv file is added to the folder, they will only need to click the Refresh button in Power BI to include the new data.

Conclusion

You are now ready to teach your workshop participants how to automate one of the most common and repetitive data consolidation tasks. Appending data is a foundational skill that, when combined with the merging techniques from the last lesson, enables managers to construct powerful and comprehensive datasets from disparate sources.

Key Takeaways for Your Workshop:

  • Appending stacks data vertically (adds rows), while merging joins data horizontally (adds columns).
  • The most important rule for appending is to have consistent column headers.
  • Append Queries as New is the safest and cleanest method for combining a known set of tables.
  • Get Data from Folder is a highly efficient, automated way to combine multiple files that follow a consistent format.

Preview of the Next Lesson:
Now that we've covered the two main ways to combine data—merging and appending—we have the tools to build a master dataset. In our next lesson, we will learn how to enrich this data by creating custom and conditional columns. This will allow managers to perform calculations and apply business logic directly within their queries.