Excel Power Query: Merge and Combine Multiple Files Automatically

You get 12 regional sales files dropped into a folder every month, and someone on your team spends an hour copy-pasting them into one master workbook. Excel Power Query can merge and combine multiple files automatically, turning that hour of copy-paste into a two-click refresh—every single month, without touching a single formula.

This is one of Power Query's most valuable features and one of the least used, because most Excel users only think of Power Query as a way to clean a single messy dataset. Its "Combine Files" functionality is built specifically for the recurring, multi-file consolidation problem that eats hours of admin time in finance, sales ops, and reporting teams every month.

The Manual Consolidation Problem

Manually combining files is repetitive and error-prone in predictable ways. Someone forgets to update one region's file before combining. A column gets renamed slightly in one file ("Region" vs "region ") and breaks a formula downstream. Someone pastes values instead of the full range and cuts off the last three rows. Every month, the process is "mostly the same" but different enough that it can't just be automated with copy-paste macros alone.

Power Query solves this differently: instead of manually opening and combining files, you point it at a folder, and it automatically detects every file matching a pattern, applies the same transformation to each one, and stacks the results into a single clean table. Add a new month's file to the folder, hit refresh, and it's included automatically.

Setting Up Automatic File Combination

Step 1: Organize Your Source Files in One Folder

Power Query's folder-based combination works best when all source files live in a single folder (or subfolders) with a consistent structure—same column headers, same general layout, even if the data itself varies. Name files consistently, like Sales_January.xlsx, Sales_February.xlsx, so it's clear what's included at a glance.

Step 2: Connect to the Folder

In Excel, go to Data > Get Data > From File > From Folder, and point it at your folder of source files. Power Query will show a preview listing every file it found, along with metadata like file name and date modified.

Step 3: Combine and Transform

Click Combine & Transform Data. Power Query prompts you to select which sheet or table to extract from each file—if your files are consistent, it picks a sample file, shows you the parsing settings, and applies that same logic to every file in the folder automatically.

This creates two things: a sample query defining how to parse one file, and a function that Power Query applies to every file in the folder. You'll see all your files' data stacked into one combined table, with a new column showing which source file each row came from—useful for traceability.

Step 4: Clean the Combined Data

Once combined, apply your normal Power Query cleanup steps once—trimming whitespace, fixing data types, removing blank rows—and those steps will apply to all files automatically on every future refresh, not just the current batch.

= Table.TransformColumns(Source, {{"Region", Text.Trim, type text}})

Because this transformation lives in the query, not in the raw data, it reapplies automatically no matter how many files you add later.

Handling Common Multi-File Challenges

Inconsistent Column Names

If one region's file has "Region" and another has "Territory," add a step in the sample query using Table.RenameColumns with a try-otherwise pattern, so the transformation doesn't fail if a column name varies slightly:

= Table.RenameColumns(Source, {{"Territory", "Region"}}, MissingField.Ignore)

Different Sheet Names Across Files

If sheet names vary (some files use "Sheet1," others use "Data"), use Excel.Workbook in your sample function and select sheets by position (first sheet) rather than by name, so the query works regardless of what each file's tab is called.

New Files Appearing Mid-Month

Because the query points at the folder itself, not individual files, any new file dropped in—correctly formatted—gets picked up on the next refresh automatically. No formula updates, no query edits, no manual re-selection needed.

Automating the Full Monthly Refresh

Refresh on Open

Set your workbook to refresh automatically when opened: Data > Queries & Connections, right-click your query, Properties, and check "Refresh data when opening the file." Anyone who opens the report gets the latest combined data instantly.

Scheduled Refresh with Power Automate

For a fully hands-off pipeline, pair this with Power Automate to refresh the workbook on a schedule if it's stored in OneDrive or SharePoint, so the consolidated report is ready before anyone even opens it each morning.

Building a Dashboard on Top

Once your files are combined into one clean table, you have a single source for PivotTables, charts, or a dynamic dashboard (see our guide on building a dynamic Excel dashboard without VBA) that updates automatically every time the underlying data refreshes.

Filtering Which Files Get Combined

Not every file in a folder should always be included—maybe you archive prior months into the same folder for reference but don't want them in the current combined table. Add a filter step on the file metadata Power Query surfaces (like "Date modified" or a naming pattern) before the combine step executes, so only files matching your current reporting period get pulled in:

= Table.SelectRows(Source, each Text.Contains([Name], "2026"))

This keeps your combined table scoped to the current period automatically, even as the folder itself accumulates a growing archive of historical files over time.

Best Practices / Pro Tips

Keep a consistent naming and folder structure for source files—Power Query's folder connection is powerful, but it assumes some consistency in what it's scanning. If file formats vary wildly between regions or departments, standardize a template first.

Always build your sample query cleanup steps to fail gracefully. Use try...otherwise patterns for anything that might vary slightly between files, so one malformed file doesn't break the entire refresh.

Archive old source files into a separate subfolder once they've been incorporated, if your combined table should reflect a rolling window rather than growing indefinitely. Point your folder query at only the active subfolder to keep refresh times fast.

Conclusion

Excel Power Query's folder-based file combination turns one of the most tedious recurring tasks in office work—manually merging multiple monthly files—into a two-click refresh that scales from 3 files to 300 without any extra effort. Once the query is built, adding new files is as simple as dropping them into a folder.

Start with your most painful recurring consolidation task, build the folder connection once, and let it run on autopilot every reporting cycle after that. The setup takes an afternoon; the time you get back every month after that is permanent.

Frequently Asked Questions

Does Power Query work with CSV and Excel files mixed together in the same folder?

It's best to keep file types consistent within one folder query, since the sample transformation is built around one file type's parsing logic. If you need to combine both CSV and Excel sources, use two separate folder queries and append the results together in a final combining step.

What happens if a file in the folder is corrupted or has a different structure?

The refresh will typically throw an error on that specific file. Using try...otherwise logic in your transformation steps helps the query skip or flag problem files instead of failing the entire refresh, so you can investigate just the one file causing issues.

Can I combine files stored in SharePoint or OneDrive, not just a local folder?

Yes. Use Get Data > From SharePoint Folder instead of From Folder, and Power Query will connect the same way, scanning all files in that SharePoint document library and combining them automatically.

How many files can Power Query realistically combine before it slows down?

Power Query handles hundreds of files without issue for typical reporting use cases. Performance depends more on file size and transformation complexity than file count—if refreshes get slow, check whether you're loading unnecessary columns or rows before combining.

Will this break if someone renames a source file after it's already been combined once?

No, as long as the renamed file still lives in the connected folder and matches the expected format, Power Query will pick it up correctly on the next refresh under its new name. The folder connection doesn't track file identity by name across refreshes—it simply re-scans the folder's current contents each time.

---

Related articles: Excel Power Query Data Transformations Guide, Build a Dynamic Dashboard in Excel Without VBA, Power Query vs VBA for Excel Automation