How to Combine Monthly Files with Power Query

What you are building

Power Query can take repeated files from one controlled folder, apply the same cleanup steps to each file, append the rows, and load one result into Excel. The key is consistency: the files need the same useful columns, the folder must not contain unrelated workbooks, and the query must be tested before you rely on it.

If your source files already contain mixed dates, numbers, and text, first review how to find inconsistent data types in an Excel column. Fixing obvious source problems early makes the folder query easier to troubleshoot.

Step 1: Prepare one controlled folder

Create a dedicated folder for this process, such as Monthly Files. Put only the files that belong in the combined result inside it. For this example, the folder contains January.xlsx, February.xlsx, and March.xlsx.

1. Make a backup of the source workbooks.
2. Confirm that each file uses the same header names, such as Date, Category, Amount, and Notes.
3. Remove temporary exports, old copies, personal backups, and unrelated workbooks from the folder.
4. Keep the file type consistent when possible. Combining Excel workbooks is easier to maintain than mixing Excel, CSV, and backup formats in one folder.

Step 2: Open the folder connector

Open a blank workbook or the workbook where you want the consolidated table to live. Then use the Excel ribbon:

1. Open the Data tab.
2. Select Get Data.
3. Choose From File.
4. Select From Folder.

Excel will ask you to choose the folder. Select the controlled Monthly Files folder, then confirm. Do not choose an individual January or February workbook; the folder is the source that makes future refreshes possible.

Step 3: Filter the file list and choose the right combine action

Power Query first shows the files it found. Treat this list as a safety check, not a screen to skip.

1. Check the Name and Extension columns.
2. Filter out any file that is not part of the monthly series.
3. Exclude hidden, temporary, or backup files. A file such as ~$January.xlsx or March backup.xlsx should not be included.
4. Choose Combine and Transform Data, not a direct combine that skips your chance to inspect the sample transformation.

The combine dialog uses one representative file as the sample. That sample determines the transformation steps that Power Query applies to the other files, so choose a normal monthly workbook with the expected headers and data types.

Step 4: Inspect the sample-file transformation

Power Query opens an editor showing the sample file and the steps applied to it. Do not load immediately. First, confirm that the transformation creates the structure you want every month.

1. Check that the first real row was promoted to column headers.
2. Check that dates are typed as dates, amounts are numeric, and text fields remain text.
3. Remove blank rows, report titles, and repeated footer rows if they exist.
4. Look down the Applied Steps list. Each step should have a clear purpose and should work for every monthly file.
5. Preview several rows and confirm that the values still belong to the correct columns.

If one month uses a different header, fix the source file or create a deliberate transformation rule. Do not quietly accept a column that becomes blank for one month. If you need a macro-based alternative for a different workflow, compare it with this VBA workbook-combining tutorial.

Step 5: Load one consolidated table and keep Source File lineage

When the preview is correct, choose Close & Load or Close & Load To. For a practical reporting workflow, load the result to an Excel Table on a new worksheet unless you have a specific reason to use the Data Model.

1. Choose the destination that fits your workbook.
2. Confirm that the output contains one row per source record.
3. Keep the Source File or Source.Name column.
4. Sort or filter the result to inspect more than the first few rows.

The source-file column is not clutter. It is your audit trail. If a total looks wrong, it lets you identify the month and workbook that supplied the row. Do not manually correct the loaded table as a permanent fix; manual edits are replaced the next time the query refreshes.

Step 6: Add next month’s file and refresh

Once the query works, the next month should be simple. Save the new workbook using the same structure and place it in the same folder.

1. Copy April.xlsx into the Monthly Files folder.
2. Open the workbook containing the query result.
3. Refresh the query from Data → Refresh All, or right-click the output table and choose Refresh.
4. Confirm that April rows appear exactly once.
5. Filter the Source File column to confirm the new rows came from April.xlsx.
6. Reconcile the April total against the source workbook.

If you remove a test file, refresh again and confirm that its rows disappear. This tests both directions of the workflow before real reporting begins.

Common mistakes and how to fix them

Final verification before you rely on the result

Run these checks after the first build and after any structural change. A query that refreshes without an error can still return the wrong rows, so verify the numbers as well as the mechanics.

• Compare the combined row count with the expected total across the monthly files.
• Reconcile at least one control total per month.
• Filter Source File and confirm every included workbook appears as expected.
• Add one test file, refresh, and verify new rows appear once.
• Remove the test file, refresh again, and verify its rows disappear.
• Check for error rows, unexpected blanks, changed headers, and unrelated backups.

Frequently asked questions

Do the files need identical formatting?

They need a consistent data structure. Cosmetic formatting can differ, but the columns and the meaning of the values should remain consistent.

Can I combine CSV and Excel files together?

It is possible, but keeping one source type per workflow is usually easier to test and maintain. If you mix types, verify the sample transformation carefully.

Why did Power Query import a backup file?

The folder connector imports files that match its filtering rules. Move backups out of the folder or filter them out before the combine step.

Can I use this on Mac Excel?

Power Query capabilities vary by Excel version and platform. Test the specific Mac installation, especially the folder connector, refresh behavior, and destination options.

Should I delete the Source File column?

Keep it until the workflow is trusted. It is one of the fastest ways to trace an unexpected row back to its source workbook.