Combine Multiple Excel Files into One Using Power Query

 


📘 Topic: Combine Multiple Excel Files into One Using Power Query ("Combine & Load")

🎯 Objective:

To import and combine all Excel files from a folder into a single table in Excel using Power Query, without manually copying and pasting.

🧭 Step-by-Step Process Flow

🔹 Step 1: Prepare Your Excel Files

  1. Make sure all Excel files you want to combine:
    • Are stored in one folder
    • Have the same structure (same headers and data format)

📂 Example folder:

makefile

CopyEdit

C:\Users\YourName\Documents\SalesReports\

  1. Each file should have one worksheet or a table with identical column headers (e.g., Date, Product, Sales).

🔹 Step 2: Open a New Excel File

  • This will be your master file where all data will be combined.

🔹 Step 3: Use Power Query to Import Files from Folder

  1. Go to the Data tab on the Ribbon.
  2. Click Get Data → From File → From Folder

  1. In the pop-up window:
    • Browse to the folder that contains your Excel files
    • Click Open

🔹 Step 4: Combine and Transform Data

  1. Excel will show you a list of all files in that folder.
  2. Click the Combine button (dropdown arrow next to it) and select:
    • Combine & Load (or Combine & Transform Data if you want to make changes before loading)
  3. In the Combine Files dialog:
    • Power Query previews the content
    • Choose the worksheet/table from one file to use as a sample
    • Click OK

🔹 Step 5: Data Is Combined and Loaded

  • Power Query will automatically:
    • Extract data from all files
    • Combine them into one master table
    • Load the combined data into a new worksheet

✅ You now have one master table with data from all Excel files.

🧾 Optional: Make It Dynamic

Any time you add a new file to the folder, just:

  1. Open the master file
  2. Go to Data → Refresh All
  3. All new file data will be included!

✅ Summary Table

Step

Action

1

Store all Excel files in one folder (same structure)

2

Open a new Excel file (master file)

3

Go to Data → Get Data → From Folder

4

Browse and select folder, click Combine & Load

5

Choose worksheet/table to combine

6

Excel loads all data into one combined sheet

7

Use Refresh All when files are updated

💡 Benefits of Power Query

Feature

Benefit

🔁 Automated updates

Just refresh to get new data

⚙️ No manual copy-paste

Saves hours of effort

📊 Consolidates data quickly

Great for reporting and analysis

📁 Reads from folder

Handles hundreds of files with ease