Question

How do I efficiently merge multiple Excel files with varying structures?
I am working with multiple Excel files in a folder, but each file has slight differences in column names or structure. I need a scalable solution in Power BI to load, standardize, and merge these files into a unified dataset. What is the best practice in Power Query to handle structural inconsistencies and ensure smooth data consolidation?

Answer 1

To efficiently merge multiple Excel files with inconsistent structures in Power BI, you should use Power Query’s Folder connector with a custom transformation function to standardize and consolidate the data.

Best Practice Steps

1. Connect to Folder of Excel Files

   • Use Get Data > Folder to load all files in the target folder.

   • In Power Query, click Combine > Transform Data (not Combine Automatically).

2. Create a Standardization Function

   • Choose one sample file and perform:

     • Sheet/Table selection.

     • Column renaming (e.g., “Cust_Name” → “Customer”).

     • Removing unwanted columns.

     • Ensuring consistent data types.

   • Convert this query to a function (right-click > Create Function).

3. Apply Function to All Files

   • Back in the original folder query, invoke the function on each file row.

   • Expand the resulting column to view combined standardized tables.

4. Handle Inconsistent Columns

   • Use Table.SelectColumns() to extract only the needed columns.

   • Add try...otherwise to handle missing columns gracefully:

try Table.SelectColumns(Source, {"Customer", "Amount", "Date"}) otherwise null