Excel Power Query: Merge All Files in a Folder

How-to basics · Updated November 19, 2026

To merge files from a folder in Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From Folder, choose the folder, then select Combine & Transform Data. Power Query will use a sample file to build a repeatable process that appends the matching files into one query; you can load the result to Excel and refresh it when the folder’s contents change.

What “merge” means in Power Query

In everyday Excel language, “merge all files” usually means stacking records from multiple files into one longer table. Power Query calls this combining files: it reads each file, applies the same transformation steps, and appends the resulting rows. For example, separate monthly sales workbooks can become a single table with one row per sale.

This is different from a Power Query merge operation, which joins two tables side by side using matching key values, similar to a lookup. If your goal is to add columns from one table to another based on an ID, use the Merge Queries command in Power Query Editor instead. The folder workflow is for collecting repeated, similarly structured files.

The folder method is useful for recurring reports because it avoids manually opening and copying each file. It is also more auditable: the query records the steps used to import and clean the data, rather than leaving a collection of one-off copy-and-paste actions.

Before combining: check the files and folder

Power Query needs to know how to interpret the files consistently. The smoothest setup is a folder containing files of the same type and a consistent layout. For example, if each workbook has a table with the same column names, Power Query can combine the rows predictably.

  • Keep unrelated files elsewhere. A PDF, temporary file, or differently formatted workbook may be included in the folder listing and cause an error or unwanted output.
  • Use a consistent data structure. Keep column headings and their meanings consistent. A file with a title, notes, or blank rows above its headers may need additional cleanup steps.
  • Choose the right level of folder. The Folder connector can include files in subfolders as well as the selected folder. If subfolders contain archive copies or unrelated data, filter them out.
  • Prefer Excel Tables for workbook sources. Named tables are generally more reliable than selecting a worksheet area whose used range may vary from file to file.
  • Keep source files intact during setup. Work from copies if you plan to reorganize, rename, or clean the files while building the query.

File names can also carry useful information. If each file is named for a month, region, or department, keep the source file name in the output so you can identify where each row originated. Power Query’s folder listing includes file metadata such as the name and folder path; you can retain or remove those columns in the query.

Steps to combine files from a folder

  1. Put the source files in one folder that Excel can access. If you use a network location, make sure it is available to the person who will refresh the workbook.
  2. In Excel, select Data > Get Data > From File > From Folder. Browse to the folder and select Open or OK, depending on the dialog.
  3. Review the file list. If it includes unwanted file types, old copies, or files from subfolders, choose Transform Data so you can filter the list before combining.
  4. When the listed files are appropriate, select Combine & Transform Data. In the combine dialog, choose the sheet or table that contains the data and inspect the preview.
  5. In Power Query Editor, review the combined columns and apply cleanup steps. For example, remove blank rows, promote the correct header row, filter out totals, or set appropriate data types.
  6. Select Home > Close & Load to load the result into a worksheet. Use Close & Load To… if you want to choose a different destination or connection option.

The exact wording in the combine dialog can differ according to the file type. For workbooks, the preview lets you select an object such as a worksheet or table. For text files, Power Query may ask about the delimiter and encoding. Check that the preview shows actual records and column names before accepting the combination.

How the combine process works

Power Query does more than paste the visible contents of each file together. It creates a query for the folder’s file list, identifies a sample file, and creates transformation logic that can be applied to each matching file. Excel may also create supporting queries or functions, often with names such as “Transform Sample File.” The exact names and arrangement can vary.

The sample file is important because it determines how Power Query interprets each file. If you choose a particular worksheet or table in the combine dialog, the generated steps use that choice for the other files too. Changes made to the shared transformation logic are then applied to each file when the main query is refreshed.

As a result, “same layout” matters more than identical file sizes or row counts. One file can contain 20 records and another 2,000, provided both expose the same kind of data in a compatible structure. A file with a different sheet name, extra introductory rows, or a different delimiter may not fit the sample-file instructions without extra transformation work.

Handle headers, columns, and file names

One common setup is to promote the first row to column headers. Make sure that happens at the correct point in the sample transformation. If each source file has its own header row, the header should be handled within the per-file transformation so the combined result contains one header row in the query, not repeated headings among the data.

Column differences need attention. If a file is missing a column, the combined output may show blank values for that field in its rows. If another file has an additional field, it may not appear automatically in the output when the generated expansion is based on the sample file’s columns. Review the query after refresh when new files may introduce new columns, and adjust the expansion or transformation steps as needed.

For traceability, retain the source file name when it is useful. The folder listing includes a Name column, and you can keep it in the result or rename it to something descriptive such as “Source file.” This makes it easier to investigate a suspicious total or locate the original report. If the file name contains a reporting period, you may also be able to derive a period field from it in Power Query.

Refresh the combined result

After the query is set up, place new files that match the existing structure in the source folder. In Excel, select Data > Refresh All to run the query again. The refreshed result reflects files currently available to the folder query and its filters; it is not a permanent snapshot of the original file set.

That behavior is useful for routine reporting, but it means a refresh can change the output. Adding an eligible file can add rows, while removing or renaming a source file can change which data is returned. Keep an archive or backup if you need to preserve a historical version of the source data. Also check whether a query is configured to refresh on opening or in the background before distributing a workbook.

The folder path is part of the query’s source settings. If the workbook is moved to another computer or a shared drive changes location, the query may no longer find the folder. A recipient may also lack permission to read a network location. Update the source path or permissions when needed, then refresh and confirm that the output is complete.

Common problems and practical fixes

  • The query reports an error for one file: Check whether that file has the expected format, sheet or table, headers, and delimiter. Filter out temporary or incompatible files, or create a separate query for a different layout.
  • Column headings appear as data: The source files may have title rows above the real headers, or the header-promotion step may be missing or placed incorrectly. Fix the per-file transformation and refresh.
  • Repeated header rows appear in the results: Each file’s header may have been left as a record. Remove those rows as part of the shared transformation, rather than deleting only the first visible header in the final table.
  • Numbers or dates look wrong: Check the data type and locale assumptions. Text that looks like a date can be interpreted differently when source files use different regional formats. Standardize the source or specify the appropriate conversion in Power Query.
  • Old or unrelated records show up: Review the file-list filters, including file extension, folder path, and hidden-file status. The connector can see eligible files in subfolders, so filter the folder path if only the top-level folder should be used.
  • New columns do not appear: The generated query may be expanding only the fields found in its sample file. Update the transformation to include the new schema and test it against all source files.

When troubleshooting, inspect the folder listing query as well as the final table. This helps distinguish a file-discovery problem from a transformation problem: if a file is absent from the listing, check its location and filters; if it is listed but errors during combination, inspect its internal structure.

Where the data goes and how to use it

For a modest dataset intended for ordinary spreadsheet work, loading the query result to a worksheet table makes it easy to filter, sort, and reference. If the combined result is large or is mainly used in PivotTables, choose a connection or the Data Model where appropriate instead of placing every row on a worksheet. A worksheet has a limit of 1,048,576 rows, so a result larger than that cannot fit on one sheet.

Power Query’s job is importing and transforming, not preserving manual edits to its output table. If you type corrections directly into cells that the query controls, a refresh can replace or rearrange that output. Put durable corrections in the source files or add them through a separate query or table designed for that purpose.

For repeatable reporting, document the folder location, expected file layout, and refresh procedure. A short note such as “drop monthly workbooks here; preserve the Sales table columns; select Data > Refresh All” can prevent confusion for colleagues who inherit the workbook.

Frequently Asked Questions

Can Power Query combine files with different names?
Yes. File names do not have to match. The files do need to be included by the folder query’s filters and compatible with the transformation built from the sample file. You can keep each name as a source column for tracking.

Does From Folder include files inside subfolders?
The Folder connector can enumerate files in subfolders as well as in the selected folder. Review the Folder Path column and filter it if you want to restrict the query to one location level or exclude archive folders.

Can I combine CSV and Excel files in one folder?
It is possible to build a more customized process, but the standard combine workflow expects files that can be interpreted using consistent transformation steps. Mixing formats often requires filtering by extension and creating separate import logic, then aligning the resulting columns.

Will the query automatically include files added later?
It can include later files when you refresh, provided they are in the source location and pass the query’s filters and match its expected structure. Select Data > Refresh All; simply saving a new file does not update the already loaded result immediately.

Can I do this in Excel for the web or on a Mac?
Power Query features and available connectors can differ by platform and release. The steps here describe Excel for Microsoft 365 on Windows. Check whether your Excel for Mac version offers the Folder connector in its Data or Get Data options; if it does not, create or edit the query in Excel for Windows. Excel for the web may let you view or refresh some queries, but it does not provide the full Windows authoring experience for every source.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.