Case Study: Loading Multiple Excel Files (Non-Standardized & Standardized Combined) in Power BI

Context

The client had a mixed bag of Excel files:

• Some with standardized column structures.

• Others with completely different formats.

They wanted all these files to be combined into one Power BI model for analysis — without manually reworking each file.

The Problem

The team faced two main challenges:

1. Standardized files were easy to combine — but non-standardized files kept breaking the process.

2. Manual fixes were eating up time — every new file with a slightly different layout needed repeated adjustments.

This was slowing reporting cycles and making the entire data preparation error prone.

The Smart Fix to Loading Multiple Excel Files: Hybrid Strategy (Separate → Standardize → Combine)

We created a two-layer approach in Power BI:

• First, separate out standardized vs. non-standardized files.

• Then, reshape the non-standardized ones into a standard format.

• Finally, combine everything into one dynamic fact table.

PART A – Handling Standardized Files

Steps were similar to Case Study 4 (multiple standardized Excels):

• Connect to Folder → Z Source.

  

• Use Transform Sample File for controlled transformations.

• End up with a dynamic query that refreshes whenever new standardized files are dropped in.

 

PART B – Handling Non-Standardized Files

Here’s where the extra effort was needed:

Step 1 – Isolate Non-Standardized Files Filter them into a separate query (e.g., Z Source – Non-Std).

Step 2 – Apply Individual Fixes

• Some files had missing headers → added headers manually.

• Some had extra notes → removed rows before the data.

• Some had mismatched column names → renamed to match standard naming.
  

Step 3 – Map to Standard Structure We created a mapping table (Excel-based) with columns:

• File Name

• Original Column Name

• Mapped Standard Column

This gave us flexibility to re-align future messy files without rewriting M code.

PART C – Combine Both Worlds

Once both sets were standardized:

1. Append Queries → merged standardized + reshaped non-standardized files.

   

2. Applied final clean-up (data types, column orders, null handling).

3. Produced one single All Excel Sales Fact Table.
   

   

Pro Tips

• Don’t fight Power Query → let it generate helper queries but stay in control of each applied step.

• Always maintain a mapping layer for non-standardized sources. It’s easier to update a table than rewrite code.

• Keep Z Source tables organized into folders (Standardized / Non-Standardized / Final Fact).

Takeaway

When your Excel files aren’t all neat and standardized, don’t panic.

Instead:

• Break the problem into two: Standardized vs. Non-Standardized.

• Fix the messy ones once with a mapping approach.

• Then combine into a single fact table.

This way, your process becomes future proof, organized, and ready for automation.