Power Query: Append Excel Files Into One Table

How-to basics · Updated November 20, 2026

Yes. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Folder, choose the folder containing your workbooks, then select Combine > Combine & Transform Data. Power Query can stack the rows from matching files into one table and refresh the result when files are added or updated.

What appending Excel files means

Appending means placing rows from one table below rows from another table. For example, if January.xlsx contains 500 sales records and February.xlsx contains 420 records with the same columns, an append produces one table with 920 records. It is a good fit for monthly reports, regional exports, survey responses, and other recurring files that share a basic layout.

Appending is different from merging. A merge matches rows using a key, such as a customer ID, and brings columns from one table alongside columns from another. Appending combines records vertically; merging combines information horizontally. If your files represent periods or locations and each row is a separate record, append is usually the operation you want.

Power Query is Excel’s data-import and transformation tool. It records the steps used to prepare data, so you can rerun those steps instead of manually copying and pasting each new batch. The result can be loaded into a worksheet table or, where appropriate, the Excel Data Model.

Append files from a folder in Excel

For a recurring set of workbooks, the folder method is usually the simplest because the query can include eligible files in that location when refreshed. In Excel for Microsoft 365 on Windows:

  1. Put the source workbooks in one folder. Avoid mixing unrelated workbooks or files with a different layout into it.
  2. Open the workbook that will hold the combined result. On the Data tab, choose Get Data > From File > From Folder.
  3. Browse to the source folder and select Open or OK. Excel displays the files it finds.
  4. Choose Combine > Combine & Transform Data. If prompted, select the worksheet or table that contains the records in the sample workbook.
  5. In Power Query Editor, review the combined columns and any automatically created transformation steps. Make corrections before loading.
  6. Choose Home > Close & Load, or use Close & Load To to select a destination and loading option.

The exact labels can vary slightly across Excel builds, but the Folder connector and Combine command are the core of the Windows workflow. Excel creates a query for the folder and supporting queries that describe how to process an individual sample file. You generally do not need to write code to use them.

Prepare the workbooks before combining

Power Query can combine files that are not perfectly identical, but consistent source files make the result more reliable. The main requirement is that the files expose the same kind of records. For instance, each monthly workbook should have a transaction table with the same field names, rather than one file containing a summary and another containing detailed transactions.

  • Use consistent column headers. “Order Date” and “Date Ordered” are different column names to Power Query, even if they mean the same thing to a person.
  • Keep the data in the same place. A named Excel table in each workbook is often more dependable than selecting a sheet whose data might move around.
  • Keep headers in the first row of the data. Avoid title rows, merged cells, totals, and explanatory notes above or within the records.
  • Use compatible data types. Store dates as dates and quantities as numbers, rather than mixing numbers and text in the same field.
  • Keep unrelated files out of the source folder. Temporary files, backups, and workbooks with other layouts can cause errors or unwanted rows.

Extra columns in some files do not always prevent a combination, but they can produce blank values for files that lack those fields. Missing columns can likewise appear as null values in the combined output. Decide whether that is acceptable or whether to standardize the inputs first.

How Power Query combines the files

When you choose a sample sheet or table, Power Query uses it to determine how to process the other files. It creates a transformation sequence for an individual file, then applies that sequence to the matching items in the folder. The transformed tables are expanded and their rows are stacked into one result.

This explains an important limitation: the sample file is not merely a preview. If the query is set up to read a particular sheet or table, other workbooks need to provide a compatible object. A renamed sheet, a missing table, or a changed layout can make a file fail or lead to unexpected output. Check the sample-file and transformation steps if a new workbook is skipped or produces errors.

Power Query also keeps a list of applied steps, such as removing unwanted columns, promoting the first row to headers, and changing data types. These steps are repeatable instructions, not edits to the source workbooks. Refreshing the query re-reads the inputs and applies the steps again.

Control columns, headers, and source details

After combining, inspect the output before loading it. Confirm that each intended source file contributed the expected records, that headers appear once rather than as data in the middle of the table, and that dates and numbers have sensible types. A common issue is that one workbook has a report title or repeated header row inside the data; those rows may need to be filtered out in Power Query.

It can also be useful to retain a file-name column so each record can be traced back to its source. Folder-based queries often expose a source name field during the combine process. If it is present, keep it when provenance matters, or add a suitable source identifier during transformation. This is valuable when investigating a bad record or explaining where a value came from.

Column order is less important than column names for appending, but a stable, clearly named schema makes the query easier to maintain. If a column is renamed in a source workbook, update the transformation steps or standardize the workbook. Do not assume Power Query will infer that two differently named columns mean the same thing.

Refresh the combined result

Once the query is loaded, use Data > Refresh All to update it. Excel reads the folder again and processes files that match the query’s settings. The source workbooks normally remain separate; refresh updates the query result rather than writing the combined data back into each source file.

For a repeatable process, keep the source folder path stable and save the query workbook somewhere that will not be picked up as an input. If the output workbook is inside the folder being combined, it may be included on a later refresh, depending on the files and filters in the query. A separate output location avoids that common source of confusion.

You can use the query’s connection properties to adjust refresh behavior, such as refreshing when the workbook opens, where those options are available in your Excel build. Automatic refresh does not remove the need to review results after a source-layout change. A successful refresh can still produce an incomplete or logically incorrect table if a new file uses different headers or contains extra rows.

Common problems and practical fixes

  • The result includes a workbook you did not intend to use: remove it from the source folder or filter the folder file list by extension, file name, or another relevant property before combining.
  • Repeated headers show up as records: filter out rows where a field contains the header text, or correct the source files so the header appears only at the top.
  • Some rows become errors or null values: check for inconsistent types, blank cells, changed column names, and unexpected values in the problem column. Review the step that assigns the data type.
  • A file is missing from the result: confirm that it is in the selected folder, is not excluded by a filter, and contains the sheet or table expected by the sample-file transformation.
  • The combined table duplicates records: check whether duplicate files or backup copies are in the folder. Appending does not automatically identify and remove duplicate records.
  • Refresh reports that the path cannot be found: confirm that the folder is still available and that the workbook has access to it. Moving a folder can invalidate the saved source path.

When troubleshooting, inspect the query preview and applied steps rather than editing the loaded output table by hand. A manual change to the output can be overwritten the next time the query refreshes.

Append queries versus combine files

Folder combine is designed to import a changing collection of files from a location. By contrast, Append Queries in Power Query Editor stacks queries that already exist in the workbook. To append existing queries, select Home > Append Queries or Home > Append Queries as New. Choose two tables, or select the option for three or more tables and add the queries to append.

Use Append Queries when you have already imported separate tables, perhaps one query per worksheet or department, and want to stack them. Use From Folder when the files arrive repeatedly in a directory and you want refresh to pick up the current collection. Both approaches append rows; they differ in how the input tables are selected and maintained.

For either approach, Power Query aligns data by column name, not simply by column position. If one table has “Region” and another has “Sales Region,” those may become separate columns in the result. Standardize names when the fields represent the same information.

Alternatives and version considerations

Manual copy and paste can be acceptable for a one-time task involving a few small files, but it is easy to omit a file, paste a header repeatedly, or introduce inconsistent formatting. Power Query is more useful when the task recurs or the input set changes, because its steps can be refreshed and reviewed.

Excel for Mac has Power Query features, but available connectors and workflow details can differ by Excel version and update channel. Check the Data tab’s Get Data options on your Mac before relying on the Windows Folder connector steps. Excel for the web also has Power Query capabilities that vary by feature and account; for the full folder-combine workflow described here, Excel desktop for Windows is the clearest baseline. If a Folder connector is not available in your build, you may need to use a supported desktop version or import the files through another available connector.

Frequently Asked Questions

Can Power Query append Excel files with different column names?
Yes, but columns are matched by name. Differently named fields generally appear as separate columns, leaving blanks where a file does not have a particular field. Rename or standardize columns if they represent the same information.

Will new files be added automatically?
They can be included when you refresh the folder query, provided they are in the selected folder and pass its filters. Refresh All updates the query; simply saving a new file does not necessarily update the loaded result immediately.

Can I combine files that have multiple sheets?
Yes, but the query must identify which sheet or table to use in each workbook. If the relevant sheet names or layouts differ, adjust the transformation logic or make the source workbooks consistent before combining.

Does append remove duplicate rows?
No. Appending stacks the rows it receives and does not automatically decide which records are duplicates. Use Power Query’s duplicate-removal options only after choosing the columns that define a duplicate for your data.

Can I combine files stored in different folders?
A single From Folder query starts from one folder location. You can create separate queries for different locations and append those queries, or reorganize the inputs into a common folder if that suits your process.

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.