How to Combine Multiple Excel Files into One (Automatically) with Power Query
9 min read
A lot of finance and operations teams rebuild the same report every month by copying data out of a dozen files. Each branch, region or month arrives as its own workbook, someone opens them one by one, pastes the rows under each other and hopes nothing was missed. If you need to combine multiple Excel files into one, Power Query can do it from a folder, and next month the work shrinks to dropping the new file in and clicking Refresh.
This guide walks through the setup step by step, then covers the problems that break most first attempts: files with different columns, different sheet names, SharePoint folders and missing checks.
What copy-paste really costs
Copying files together looks cheap because each step is small. The costs show up elsewhere. A file gets pasted twice, or a row of headers lands in the middle of the data, or someone pastes over last month’s formulas. None of it is visible until the totals look odd in a management meeting.
There is also the time. If consolidating takes a person eight hours a month, that is roughly 96 hours a year spent on work that adds nothing to the numbers. On one telecom reporting project I worked on, where data came from SAP, a data warehouse and an Access database, we cut reporting time by 70%.
What you need before you start
Power Query is built into Excel 2016 and later and into Microsoft 365, under Data > Get Data. The steps below are for Excel for Windows. Excel for Mac has Power Query too, but its list of data sources has historically been shorter, so check that From Folder is available in your version before you plan around it.
Put the source files in one folder that holds nothing else. Power Query reads every file in the folder and its subfolders, so stray files (a backup, a PDF, someone’s draft) end up in your data unless you filter them out.
For the example in this guide, the folder C:\Reports\Sales holds three monthly exports:
| File | Columns |
|---|---|
| Sales_2026-01.xlsx | Date, Store, Amount |
| Sales_2026-02.xlsx | Date, Store, Amount |
| Sales_2026-03.xlsx | Date, Store, Amount, Channel |
March has an extra Channel column. That matters later.
Combine files from a folder, step by step
- In a new workbook, go to Data > Get Data > From File > From Folder.
- Browse to the folder and click Open. Excel shows a list of the files it found, with columns such as Name, Extension and Date modified.
- Click Combine > Combine & Transform Data. (Combine & Load skips the editor and loads straight to a sheet, which is fine for a quick test but leaves you no chance to clean anything.)
- In the Combine Files dialog, pick the sample file in the first drop-down and the sheet or table that holds the data. Power Query uses this sample as the pattern for every file.
- Click OK. The Power Query Editor opens with all rows from all files stacked in one table, plus a
Source.Namecolumn that tells you which file each row came from. - Check the data types (dates as Date, amounts as Decimal Number), then click Close & Load.
Behind the scenes, Power Query creates a group of helper queries: a sample file, a parameter, a function called Transform File and a query called Transform Sample File. Any step you add to Transform Sample File (removing a header row, renaming columns, filtering out totals) runs on every file in the folder. That is where most of your cleaning should go.
From now on, adding April’s file to the folder and pressing Data > Refresh All (Ctrl+Alt+F5) brings it in.
Filter out files that don’t belong
Add a filter right after the Source step of the main query so only real data files get combined. Excel creates hidden lock files that start with ~$ while a workbook is open, and those break the combine step if someone has a file open during refresh.
= Table.SelectRows(Source, each Text.Lower([Extension]) = ".xlsx"
and not Text.StartsWith([Name], "~$"))
Text.Lower is there because extensions sometimes arrive as .XLSX.
When files have different columns
This is the most common question on forums such as r/excel, and it catches nearly everyone the first time. With the default setup, our combined table has Date, Store and Amount, but March’s Channel column is missing.
The cause is in the main query. The step that expands the combined data takes its column list from the sample file only:
= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File",
Table.ColumnNames(#"Transform File"(#"Sample File")))
If the sample is January, any column that only exists in later files is dropped without warning. To include every column from every file, replace the column list with the union of all column names:
= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File",
List.Union(List.Transform(#"Removed Other Columns1"[Transform File], Table.ColumnNames)))
Now the result includes Channel, filled for March and empty (null) for January and February:
| Source.Name | Date | Store | Amount | Channel |
|---|---|---|---|---|
| Sales_2026-01.xlsx | 05/01/2026 | Zagreb | 1,250.00 | null |
| Sales_2026-02.xlsx | 03/02/2026 | Split | 980.00 | null |
| Sales_2026-03.xlsx | 02/03/2026 | Zagreb | 1,410.00 | Online |
Step names such as #"Removed Other Columns1" depend on your query, so use whatever name the previous step has in your Applied Steps list.
One more trap: the main query often ends with an automatic Changed Type step that lists column names. If a column disappears from the files, refresh fails with an error saying the column wasn’t found. I delete that step and set types myself after the expand step, only for the columns I’m sure will always be there.
Same data, different headers
Sometimes the columns are the same but the headers differ: Amount in one file, AMOUNT with a trailing space in another. Power Query treats those as two columns. A small step in Transform Sample File, after the headers are promoted, fixes the easy cases:
= Table.TransformColumnNames(#"Promoted Headers", each Text.Proper(Text.Trim(_)))
This trims spaces and applies consistent capitalization, so AMOUNT and amount both become Amount. When headers differ in wording (Amt against Amount), use a small mapping table with old and new names and rename with Table.RenameColumns. That keeps the rules visible to whoever maintains the file after you.
When sheet names differ
Your sample file’s data may sit on a sheet called January, while the next file uses February. The default navigation step picks the sheet by name, so every other file fails with an error.
Open Transform Sample File and look at the step right after Source. It will look something like Source{[Item="January",Kind="Sheet"]}[Data]. Replace it so it takes the first sheet in each workbook, whatever its name:
= Table.SelectRows(Source, each [Kind] = "Sheet"){0}[Data]
This assumes the data is on the first sheet in every file, so confirm that with whoever produces the exports. If the source files use proper Excel tables with a fixed name, selecting by table name is even more reliable.
Combine files from a SharePoint or OneDrive folder
Many teams keep their monthly files in SharePoint or a Teams channel rather than on a local drive. Power Query has a connector for that:
- Go to Data > Get Data > From File > From SharePoint Folder.
- Enter the site URL, not the folder URL. For example
https://contoso.sharepoint.com/sites/Finance. Microsoft’s documentation notes that only modern SharePoint sites are supported. - Sign in with your organizational (Microsoft 365) account when asked.
- Click Transform Data rather than Combine. You get a list of every file in the site’s document libraries.
- Filter the Folder Path column to the folder you want, then use the Combine Files button on the Content column.
From there the setup is identical to a local folder. OneDrive for Business is a SharePoint site under the hood, so the same connector works with your OneDrive URL, which looks like https://contoso-my.sharepoint.com/personal/name_contoso_com.
On large sites the file list can take a while to load, because the connector lists files across the whole site before you filter. Filtering on Folder Path as early as possible keeps refresh times reasonable. Also avoid #, % and $ in file names; Microsoft lists them as a known cause of authentication errors with this connector.
Add checks and set up one-click refresh
A combined table is only useful if you can trust it. These are the checks I add to almost every consolidation:
- A row count per file. In Power Query, reference the combined query and use Group By on
Source.Namewith a count of rows. A file that suddenly has 12 rows instead of 900 stands out at once. - A control total. Compare the combined total of Amount with the total from the source system, or with the sum of the totals each file reports.
- A list of files included in this refresh, with their modified date, so you can see whether last month’s file was replaced or added twice.
Here is what the row count check looks like after a refresh:
| Source.Name | Rows |
|---|---|
| Sales_2026-01.xlsx | 912 |
| Sales_2026-02.xlsx | 887 |
| Sales_2026-03.xlsx | 14 |
March clearly needs a look before anyone reads the report.
For refreshing, right-click the query in the Queries & Connections pane, choose Properties and tick Refresh data when opening the file if you want the data to update every time the workbook opens. Otherwise Data > Refresh All updates every query, PivotTable and chart that depends on it.
When it gets complicated
The folder setup above handles a surprising share of real reporting work. It gets harder when the files come from different systems with different layouts, when product or store names need mapping before they can be compared, or when the report needs rules about which source wins when two files disagree. In my experience the import itself is the quick part. The matching, checks and edge cases take most of the effort.
Frequently asked questions
Can Power Query combine Excel files with different columns?
Yes. By default it keeps only the columns from the sample file. Replace the column list in the expand step with List.Union(List.Transform(..., Table.ColumnNames)), as shown above, and every column from every file is included, with nulls where a file doesn’t have that column.
Do the source files need to stay in the same folder?
Yes. The query points to a folder path. If the folder moves, update the path in the Source step (or in Data Source Settings) and refresh.
Will it pick up new files automatically?
It picks them up on the next refresh. Drop the new file into the folder, then use Data > Refresh All or open the workbook if you set it to refresh on open.
Can I combine CSV files the same way?
Yes. The From Folder steps are the same; Power Query detects the CSV format from the sample file. Keep CSV and Excel files in separate folders, because one folder query applies one pattern to every file.
Does this work in Excel for the web?
Excel for the web can’t read folders on your computer, so set up and maintain a folder query in desktop Excel for Windows.
Get it built for you
If your team spends days each month pulling files together, I can build the consolidation, checks and one-click report in the Excel you already use. Have a look at Report Automation: fixed price agreed after a free 20-minute call, with a handover video so your team can run it without me.