Case Study: Loading Multiple Standardized Excel Files in Power BI

Context

Client had multiple Excel files, all in the same structure (standardized columns), sitting in a folder.

They wanted to load these files into Power BI without repeating steps for each file manually — and automate future additions to the folder.

The Problem

The team was:

• Manually importing each Excel file one by one

• Repeating transformations again and again

• Risking inconsistencies across files

• In some cases, relying too heavily on Power BI's auto-generated logic, making things hard to control

This wasted time and created unnecessary room for error every time new files were added.

The Smart Fix: Folder Connector + Controlled Combine Logic

We switched to a folder-based approach, using Power BI's Combine functionality in a clean and structured way.

PART A - Step-by-Step Process to Load the Data

Step 1 – Connect to Folder from Get Data

Step 2 – Get its path

Step 3 – Click on Transform Data

Step 4 – Now we start referencing

As we’ve seen in previous blogs — we must think for the future. A future where we're frustrated because the client introduced different sources that aren’t standardized. But if we fix it now, we eliminate that future pain.

Delete all applied steps except Source.

Rename this table as Z Source.

Disable their loads.

Step 5 – Create a reference of Z Source

Let’s say you connected to a root folder that contains many random files and subfolders, and one correct subfolder deep inside with your actual fact CSVs.

Step 6 - Organize - Split by Delimeter

Go to the Folder Path column

Use Split by Delimiter → choose \ (backslash)

Split at each occurrence

In the advanced split window, increase the number of columns to something like 10, 15, or 20 as needed

Step 7 – Identify the specific path that holds your actual fact files

Filter to that path using Folder Path columns

Rename the query to something like Z Source - Monthly Sales

Optionally move your sources into a group called Sources.

Step 8 – Create a final reference for Fact Table from Z Source - All Sales

Lets Call in All Monthly Sales

Step 9 - Lets finally get all the data!!

Click the Double Drill icon next to the Content column

Let Power BI generate a sample file — if it looks good, proceed

Power BI will create Helper Queries like Transform Sample File, a function, and the final combined query

Note - 
Transform Sample File has a Sample of one of the files out of the many in the Folder. Every step in this is applied to "All Months Sale" which was the file we expanded.

That’s your final dynamic fact table. No more repeating steps for every file.

Step 10 - Final Step

Once all looks good — click Close & Apply.

Now you have:

• A future-proofed data load setup

• Clean references

• Clear transformation logic

• A fully combined and dynamic fact table

Key Difference – Transform Sample File vs. Combined Final File

If, for example, all your CSVs had column headers in Row 2, and you promote headers in the Combined Actual File, Power BI will promote headers for the merged dataset — but every file still has its headers repeated throughout. So you'd then need to filter those out and only keep valid rows.

However, if you apply header transformation in the Transform Sample File, Power BI will apply it cleanly to each individual file before combining, giving a cleaner result with less post-filtering.

So you need to understand how to navigate both. Each has its pros and cons — and it depends on the case study when to use what.

Pro Tip – 

Be the Owner of Applied Steps

When you do Promote Headers, Power Query — being the nice machine made by nice people — will automatically apply a Changed Type step.

Please be aware of this, because sometimes the inferred data types may not match what your business logic needs.

If that happens:
Simply delete the Changed Type step

Apply your own data types manually

The mindset here is important: You are RESPONSIBLE for every applied step. You don’t want to look weak in front of management with the excuse, “I don’t know, it happened automatically.”

This tip is more about maintaining strength in your Trustworthiness trait — people need to know that every step in the report has your conscious approval behind it.

Takeaway

If your Excel or CSV files are standardized, then yes — you must combine them.

But combine them the right way:

• Build a proper Z Source

• Organize your structure with references

• Let Power BI handle the combining — but keep control by understanding the sample file logic

• Let Power BI work for you — not the other way around.

• 
  

Want to see a sample of this logic or replicate it for your team? Reach out!