Every month it is the same chore. A new export lands in a folder, and you open it, copy the rows, and paste them under last month's. Do that twelve times a year, across several reports, and the hours add up. Worse, one slip corrupts the whole file. Power Query's From Folder feature ends the ritual. It reads every file in a folder and blends them into one table, ready to refresh.
This guide shows how to combine files the smart way. First, it explains the monthly-file problem. Then it covers From Folder, the sample file, and the helper function. Best practices and a full troubleshooting section follow. By the end, new files will fold in with a single click.
The Monthly-File Problem
Recurring exports arrive as separate files. Each month brings a fresh copy, same shape. Combining them by hand is slow and risky. So a repeatable method saves real time.
What From Folder Does
From Folder points Power Query at a whole folder. It reads each file and stacks the rows. The result is one clean, combined table. Better still, new files join on refresh, as below.
So you build the process once. After that, the folder does the work. Drop in next month's file, then refresh. As a result, the report updates itself.
How to Combine Files
The setup is quick and mostly guided. You point Excel at the folder. Then you choose Combine and Transform. Power Query does the stacking for you.
The Sample File and Helper Function
Behind the scenes, Power Query is clever. It picks one file as a sample to shape. Then it turns your steps into a function. That function runs on every file, as below.
Keep the Source Filename
Combined rows can lose their origin. You may forget which file a row came from. A Source.Name column solves that neatly. It records the filename beside each row.
Add New Files, Just Refresh
This is the payoff of the whole setup. A new file needs no rebuild at all. You simply drop it into the folder. Then one refresh pulls it in, if you follow a few habits, as below.
Troubleshooting Folder Imports
All three problems below are the most common. Each has a clear cause and a quick fix.
The files have mismatched columns
Combining assumes every file shares a structure. So one odd file creates gaps or errors. First, open the offending file and compare its headers. Then fix the column names so they match the rest. If a file is truly different, move it out of the folder. You can also reorder columns by name in the sample transform. After that, the combine lines up cleanly.
The folder holds mixed file types
A folder often collects stray files over time. A rogue PDF or image will break the combine. First, do not combine straight from the raw list. Instead, filter the Extension column to your type. Keep only .csv or only .xlsx, for example. Then run Combine on that filtered list. So only the right files ever get processed.
Header rows appear as data
Sometimes each file's header repeats inside the data. This happens when headers are promoted at the wrong step. First, open the sample file transform. Then check where Promote Headers runs. It should run once, after the files combine. Remove any duplicate promote step inside the function. After that, the headers sit at the top only.
Frequently Asked Questions
- How do I combine multiple files with Power Query?Use Data, Get Data, From File, then From Folder. Point it at the folder and click Combine and Transform. Power Query stacks every file into one table. So all your files load together.
- Do the files need the same columns?Yes, the files should share the same structure. So each one needs matching column names. A different file creates gaps or errors. Therefore fix the headers or move odd files out.
- How do I add a new file to the combined table?Just drop the new file into the folder. Then click Refresh in Excel. Power Query reads it and stacks its rows. So no rebuild is ever needed.
- How do I know which file each row came from?Keep the Source.Name column after combining. It records the filename beside each row. You can even extract the month from it. So grouping by file becomes easy.