Step-by-Step Guide to Combining Multiple Excel Files Using Power BI Folder Connection

In many real-world scenarios, data is not stored in a single file. Instead, you receive multiple Excel files—daily reports, weekly timesheets, or monthly sales data. Manually combining these files is time-consuming and error-prone.

Power BI’s Folder Connection feature solves this by automatically combining all files into one dataset and updating it whenever new files are added.

When Should You Use Folder Connection?

Use this method when:

• You receive multiple files with the same structure

• Files are added regularly (daily/weekly/monthly)

• You want to automate data consolidation

• Manual append is becoming inefficient

This is commonly used for timesheets, logs, and recurring reports.

Step 1: Organize Your Files Properly

Before connecting Power BI:

• Place all Excel files in a single folder

• Ensure all files have:

  • Same column names

  • Same structure

• Remove unnecessary or test files

Example:

Step 2: Connect to the Folder

1. Open Power BI Desktop

2. Click Home → Get Data → Folder

3. Browse and select your folder

4. Click OK

Power BI will list all files inside the folder.

Step 3: Combine Files

After selecting the folder:

1. Click Combine & Transform Data

2. Power BI will:

   • Open a sample file

   • Detect structure

   • Create a transformation template

This template is applied to all files automatically.

How the Sample File and Combine Process Work

·         When you select Combine & Transform Data, Power BI automatically chooses one file from the folder as a Sample File. This file is used only as a reference to understand the structure of the data, including worksheets, tables, column names, and data types.

·         Power BI then creates a transformation function based on the sample file. Any data cleaning steps, such as removing columns, changing data types, filtering rows, or renaming fields, are stored within this function.

Once the function is created, Power BI applies the same transformation steps to every file in the folder that follows the same structure. The processed data from all files is then appended together into a single consolidated dataset.

For example, if the folder contains:

• Timesheet_Week1.xlsx (100 rows)

• Timesheet_Week2.xlsx (120 rows)

• Timesheet_Week3.xlsx (90 rows)

Power BI applies the same transformation logic to all three files and combines them into a single dataset containing 310 rows.

When a new file such as Timesheet_Week4.xlsx is added to the folder, Power BI automatically applies the existing transformation function during refresh and includes the new data in the consolidated dataset without requiring any manual changes.

Step 4: Understand the Template File

Power BI uses one file as a reference (template).

Why this matters:

• Defines column structure

• Ensures consistency across all files

If files have different structures, combining will fail or produce incorrect results.

 Step 5: Clean and Transform Data

Once combined, Power Query Editor opens.

You can now:

• Remove unwanted columns

• Fix data types

• Filter rows

• Rename columns

These transformations will apply to all files in the folder.

Step 6: Filter Files Dynamically

You can control which files are included.

Examples:

• Include only recent files

• Filter by file name or date

• Exclude temporary files

This is useful for managing large datasets.

Step 7: Load the Combined Data

After transformation:

• Click Close & Apply

Power BI will load a single consolidated dataset from all files.

Step 8: Automate Future Updates

This is where the real power comes in.

When new files are added to the folder:

1. Place the new file in the same folder

2. Open Power BI

3. Click Refresh

Power BI will:

• Detect new files

• Apply the same transformations

• Update your report automatically

No need to manually append files again.

Real-World Example

Scenario: Weekly Timesheet Reporting

Instead of:

• Importing each file manually

• Appending every week

You:

• Store all timesheets in one folder

• Connect Power BI to that folder

• Refresh data weekly

Result: Fully automated reporting.

Common Mistakes

• Files with inconsistent column names

• Different formats across files

• Including unrelated files in the folder

• Modifying structure after setup

Best Practices

• Maintain a standard template file

• Use clear file naming conventions

• Store only relevant files in the folder

• Validate combined data regularly

• Avoid manual edits outside Power BI