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 41

Importing and Combining Data from Multiple Excel Files with Power Query

Recommended resources

Introduction

Hello! Welcome to the first lesson in our module on Advanced Data Consolidation.

In the previous modules, we focused on building dynamic reports and professional dashboards. You learned to summarize data with PivotTables and visualize it with charts. However, all that work depended on having your data neatly organized in one place. In the real world, especially in MIS roles, data often arrives in pieces—like monthly sales reports, weekly incident logs, or daily updates from different teams, each in a separate Excel file.

So far, you would have had to manually copy and paste this data together, a process that is not only time-consuming but also prone to errors. Today, we're going to solve that problem.

This lesson introduces Power Query, Excel's powerful data transformation engine. Our goal is to use Power Query to import and combine data from multiple Excel files within a folder. This skill is a cornerstone of modern data analysis in Excel. For an MIS Executive, mastering this process means you can automate your most repetitive data collection tasks, freeing up your time for analysis and ensuring your reports are always based on the complete, most recent data.

1. What is Power Query and Why Should You Use It?

Before we jump into the "how," let's quickly cover the "what" and "why." Power Query (also known as "Get & Transform Data" in the Excel ribbon) is a tool designed to help you discover, connect, combine, and refine data from a wide variety of sources.

For your goal of becoming an MIS Executive, Power Query is not just a nice-to-have; it's a game-changer. It automates the entire process of gathering and cleaning data, which is often the most labor-intensive part of creating any report or dashboard.

Excel Power Query: Combine Multiple Excel Files (2025 Guide)

This article from Accelerate Excel provides an excellent overview of Power Query and makes a strong case for why it's superior to manual methods. Pay close attention to the table comparing the two approaches.

Read the first three sections of the article: The introduction (starts with "If you regularly merge data..."). "What is Power Query?" "Why Use Power Query for Data Consolidation?" Focus on the benefits it provides: automation, reduced errors, and reproducibility. This directly addresses the need for structured, reliable processes you've experienced in your previous roles.

2. The Step-by-Step Process for Combining Files

Now let's get hands-on. The process of combining files can be broken down into four main stages:

  1. Prepare: Organize your source files.
  2. Connect: Point Power Query to the folder containing the files.
  3. Combine & Transform: Tell Power Query how to merge the files and apply any necessary cleaning steps.
  4. Load: Load the final, consolidated table into Excel.

Step 1: Prepare Your Files

Before you even open Excel, good preparation is key. As the article mentioned, this simple step prevents many headaches later.

  • Create a dedicated folder: Place all the Excel files you want to combine into a single folder. Make sure no other files are in this folder.
  • Ensure consistency: For this process to work smoothly, the files should have a consistent structure. This means the data you want to combine should be on a sheet with the same name (e.g., "Sheet1") in every file and have the same column headers.

Step 2: Connect to the Folder

This is where we tell Excel where to find our data. You'll start from the Data tab on the ribbon.

Importing Data from a Folder using Power Query in Excel
This image shows the exact menu path to begin the process: Data > Get Data > From File > From Folder.

Step 3 & 4: Combine, Transform, and Load

This is the core of the process. Once you connect to the folder, Power Query shows you the files it found. From there, you'll enter the Power Query Editor to combine them. A crucial concept here is the "Sample File." Power Query uses the first file as a template. Any cleaning steps you apply to this sample file will be automatically applied to all the other files before they are stacked together.

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

This video by Leila Gharani provides a clear, end-to-end demonstration of the entire process. It's one of the most common and powerful uses of Power Query, so pay close attention to each step.

Watch the video from (01:28) to (07:45). I've broken down what to watch for in each segment: Connecting & Filtering Files (01:28 - 02:37): See how to connect to the folder and, importantly, how to filter the list of files if needed (e.g., if your folder contains non-Excel files or temporary files). Combining and Understanding the Sample File (02:37 - 04:35): This is a key part. Notice how Power Query asks you to pick a sheet from a sample file. The video explains the queries that are automatically created. Cleaning the Data in the Sample File (04:35 - 07:13): This is the most critical concept. The data in the video isn't perfectly clean; it has extra rows at the top and blank rows at the bottom. Watch how she makes the cleaning steps (like Remove Top Rows and Use First Row as Headers) in the sample file query. This ensures every file is cleaned correctly before being combined. Loading the Data (07:13 - 07:45): Finally, see how to use Close & Load To... to load the combined data as a PivotTable Report or a Table. For now, we will focus on loading to a table.

This image provides a great visual summary of the entire workflow you just saw in the video.

Combining Multiple Excel Files from a Folder using Power Query
This diagram illustrates the three main stages: (1) selecting the source folder, (2) configuring the combine settings using a sample file, and (3) viewing the final consolidated data in an Excel table, complete with a 'Source.Name' column for tracking.

3. Your Turn: Consolidate Expense Reports

Now it's time to put this into practice. We'll use a set of sample expense report files. Your task is to combine them into a single master list.

  1. Download the practice files:

Combine multiple Excel files using Power Query

The author of this article, Chandoo, has kindly provided the sample files used in his tutorial. We only need to download them for our practice exercise.

In the section titled "Download sample files to practice this", click the link to download the .zip file. You do not need to read the rest of the article for this exercise.

  1. Set up your folder:

    • Unzip the downloaded file (combined-data.zip).
    • You will see a folder named reports. Inside are three Excel files (TR-EMP-1024.xlsx, TR-EMP-1025.xlsx, TR-EMP-1026.xlsx).
    • Create a new folder on your computer (e.g., on your Desktop) and name it Monthly Expenses.
    • Copy the three Excel files from the reports folder into your new Monthly Expenses folder.

  2. Combine the files using Power Query:

    • Open a new, blank Excel workbook.
    • Follow the steps from the video to connect to your Monthly Expenses folder.
    • When the "Combine Files" dialog appears, you'll see two sheets available: travel details and expense details. The expense details sheet is already in a clean, tabular format. Select expense details and click OK.
    • Since this data is already clean, you won't need to perform any transformation steps in the sample file.
    • Once Power Query shows you the combined data, click Close & Load.

You should now have a new worksheet in Excel with a single table containing all the itemized expenses from the three separate files. Notice the Source.Name column that Power Query automatically added—this is incredibly useful for tracing data back to its original file.

4. The Power of Refresh

You've built a repeatable process. What happens when you get a new file for the next employee?

  1. Find the file named TR-EMP-1027.xlsx in the combined-data folder you downloaded.
  2. Copy and paste this new file into your Monthly Expenses folder.
  3. Go back to your Excel workbook containing the combined table.
  4. Right-click anywhere inside the table and select Refresh.

Instantly, the data from the new file is added to the bottom of your table. This one-click update is the real power of Power Query. You've built an automated system for data consolidation.

Conclusion

In this lesson, you've learned one of the most practical and time-saving skills in modern Excel. You are no longer just an Excel user; you are starting to build automated data workflows.

Key Takeaways:

  • Power Query automates data consolidation. It replaces manual, error-prone copy-pasting with a repeatable, one-click refresh process.
  • The "Get Data from Folder" command is the starting point for combining multiple files.
  • Power Query uses a Sample File as a template; transformations applied to the sample are applied to all files before combining.
  • To enable this automation, your source files must have a consistent structure (e.g., same sheet names and column headers).
  • Adding new files to the source folder and clicking Refresh is all it takes to update your master dataset.

In our next lesson, we'll continue exploring Power Query. We will tackle another common scenario: appending data from multiple worksheets within a single Excel file into a master table.