Combine Monthly Excel Files From a Folder With Power Query

Power Query can combine monthly Excel files from a folder into one refreshable table without copying rows. Keep the workbooks in a consistent layout, check their column names, and exclude backup copies before you combine them.

Folder import is the right choice for repeated monthly files

Power Query's folder import is a good fit when each monthly workbook contains the same kind of rows and you expect more files to arrive. It reads the files in a chosen folder, applies the same preparation steps to each one, and puts the results into a single Excel table. When a new month arrives, you add its workbook to the folder and refresh the query instead of copying and pasting rows.

This is an append job: January's rows go underneath February's rows. In Power Query, a merge is a different operation that matches records across tables using shared values. You do not need that operation to stack monthly spreadsheets.

A manual copy and paste may be enough for a one-off task with a small, fixed set of files. Folder import becomes more useful when the task repeats and someone needs to see where each row came from. It will not make inconsistent workbooks consistent by itself, so set up the source files before building the query. If the files arrive as email attachments, saving attachments to a folder can be a separate step. Check that the destination holds only files you intend to include.

A consistent source table prevents avoidable errors

Give every monthly workbook the same columns, with the same header spelling, before combining Excel files in a folder with Power Query. An Excel Table with the same name in each workbook is particularly helpful: it gives the folder import a consistent object to select, even if worksheets have different names. If you use worksheets instead, keep the data on the same selected sheet with headers in the same position.

Consider a folder containing January.xlsx and February.xlsx. Both contain a Date, Customer, and Amount column. The combined table should place their sales rows underneath each other. If February uses Client where January uses Customer, stop and resolve that difference before relying on the result. Depending on the steps Power Query created from its sample file, the changed header may produce an error, leave an empty field, or fail to appear as a separate column.

The simplest fix for occasional mistakes is to correct February's header in the source workbook, save it, and refresh. If Client is an intentional change that future files will use, decide on a standard header and update the older files or the query's preparation steps accordingly. Do not assume that a successful refresh proves every expected column was included.

The folder preview is where unwanted files are excluded

Filter the folder's file list before combining its contents. In desktop Excel, start with Data, then Get Data, From File, and From Folder. Choose the folder and select Transform Data so you can inspect its file list in Power Query. The exact labels can vary by Excel edition, but the useful point is the preview before the workbook contents are combined.

Keep the intended Excel workbooks and exclude temporary files, old exports, and backups. Power Query shows details such as file name, extension, and folder path that you can use for these filters. Check the folder path if there are subfolders: a backup stored below the chosen folder may otherwise be included. Keep the finished combined workbook outside the input folder so a refresh does not pick it up as another source file.

A filter based only on a name pattern needs care. If you exclude files containing the word backup, confirm that no real monthly file uses that word. The safer habit is to keep backup copies outside the import folder and use the preview to verify which files remain. If another workflow files incoming attachments into SharePoint, auto-filing attachments to SharePoint addresses delivery; the Power Query import still needs its own check of included files.

Combine and load the rows into an Excel table

After filtering the folder list, combine the Content column and select the table or worksheet that holds the monthly data. Power Query uses a sample file to build preparation steps that it then applies to the other files. Inspect the preview before loading: check that the first data row is not being treated as headers, the expected columns appear, and dates and amounts have sensible types.

Keep the source file name column when it is available. Power Query commonly includes it as Source.Name in a folder combination. That column makes it possible to trace a row back to its workbook and spot an unexpected backup copy. Rename it to something clear if that helps your team, but do not discard it just to make the final table look shorter.

Use Close & Load to place the result in an Excel table. Adding a later monthly workbook and choosing Refresh updates the query result; you should not paste its rows into the loaded table. To append multiple Excel workbooks without copy paste, put the new workbook in the input folder, confirm it follows the same layout, and refresh. Leave any hand-entered notes outside the loaded table, because a refresh can replace its contents.

A changed header needs a check before the table is trusted

Check column names and missing values whenever a monthly workbook changes. In the January and February example, suppose February arrives with Client instead of Customer. Before refresh, compare its headers with the agreed template. If the change was accidental, rename Client to Customer in February.xlsx and save the workbook. Then refresh and inspect February's rows in the combined table.

If you discover the change after refresh, open the Power Query editor and look for errors in the query and its preparation steps. Also inspect the loaded Customer column for blank February entries and check whether an unexpected Client column appeared. The outcome depends on how the sample-file transformation was built, so do not rely on an error message to catch every variation.

For a deliberate change across future files, a practical no-code approach is to settle on one header and update the source workbooks before importing them. If old and new layouts must coexist, prepare them as separate queries, rename the corresponding columns to the same header, and append the prepared queries. That is more work than a single folder combination, but it makes the rule visible rather than silently treating two names as different data.

A file-level check catches duplicate imports

Check which workbooks contributed rows before treating the combined table as final. A refresh does not normally add another copy of the same rows to the existing query result. Duplicate rows usually mean the input contains the same records more than once, such as February.xlsx alongside February backup.xlsx, or the same month's export stored under another name.

Use the retained source file name column to review the distinct files represented in the result. A PivotTable can show a row count and amount total by source file. Compare those entries with the workbooks you meant to import and with the totals in the source files. If February and its backup both appear, move the backup outside the input folder or adjust the folder query's file filter, then refresh and check again.

Avoid using Remove Duplicates across the entire combined table as a substitute for this check. Two legitimate transactions can have identical visible values, while a duplicated export may contain rows that look slightly different. Excluding the unwanted file preserves the intended data and gives you a clearer explanation of the result. Repeat the file and column checks whenever someone changes the folder, template, or query.

Sources consulted

• Microsoft Support (support.microsoft.com)
• Microsoft Learn (learn.microsoft.com)

Start a free course