Power Query

Combine monthly files automatically

7 min read

Point Power Query at a folder and let new monthly files append themselves. No more copying and pasting twelve exports into one sheet.

The problem

Every month a new file arrives: a bank export, a sales report, a payroll register. Every month someone opens it, copies the rows, pastes them under last month's, and hopes nothing was missed. It works until a month is skipped, pasted twice, or pasted a row too low. Then the totals stop tying out and nobody knows why.

Power Query solves this by reading every file in a folder, stacking them into one table, and remembering how. When next month's file lands in the folder, you refresh and it is included. This guide shows how to set it up and where it tends to break.

The idea in four boxes

A folderEvery monthly file lives here.
→
Read each fileUsing one sample as the template.
→
Stack the rowsAll files become one list.
→
One tableRefreshes with one click.

The one requirement is consistency. Every file must have the same columns with the same headings, because Power Query builds its steps from a sample file and then repeats them for each of the others.

Set it up in seven steps

You need Excel 2016 or later, or Microsoft 365. The menu names below are for Windows desktop Excel.

  1. Put the files in one folder. Same layout every month. Name them so they sort naturally, such as 2026-03 Sales.xlsx.
  2. Start the connection. In a new workbook, go to Data, then Get Data, From File, From Folder, and choose the folder.
  3. Check the file list. Excel shows every file it found. If the list looks right, choose Combine & Transform Data.
  4. Pick the sample. Leave the sample file as the first file, and select the sheet or table that holds the data. Click OK.
  5. Clean it once. The Power Query editor opens with the combined result. Set the data types, remove blank rows or total lines, and rename columns. Every step you add here applies to every file.
  6. Load it. Choose Close & Load. The combined data appears as a table.
  7. Next month, drop the new file into the folder and choose Data, then Refresh All.

Excel also creates a few helper queries (a sample file, a parameter and a function) grouped in the Queries pane. You can ignore them. They are what repeats your cleaning steps for each file.

See it work

Drop files into the folder, then refresh the query. Try adding April, then try the file with a renamed column.

Folder: Monthly Sales
    Your table
    Source.NameDateCustomerAmount

    Keep track of which file each row came from

    Power Query adds a column called Source.Name with the file name of every row. This is useful for tracing numbers back to their file. If your files are named with the year and month first, you can turn the name into a real date. In the editor, choose Add Column, then Custom Column, and enter:

    Date.FromText(Text.Start([Source.Name], 7) & "-01")

    For 2026-03 Sales.xlsx, the first seven characters are 2026-03, so the result is March 1, 2026. Set the new column's type to Date.

    What goes wrong, and how to fix it

    SymptomUsual cause and fix
    An error that says a column "wasn't found"One file has a different heading than the sample, for example Amt instead of Amount. Correct the file, or fix the heading in the source system.
    Odd rows or a file you did not expectThe folder contains other files. Excel creates a temporary file starting with ~$ while a workbook is open. In the query, filter the Name column to exclude names that begin with ~$, and filter the Extension column to .xlsx.
    Total lines or header text in the middle of the dataRemove them in your cleaning steps, for example by filtering out rows where the Date column is empty.
    Doubled totalsThe same month is in the folder twice, such as a corrected version saved next to the original. Keep one file per period.
    The query breaks on someone else's computerThe folder path is stored in the Source step. Use a shared location both people can reach, or edit the path.
    New data does not show upQueries do not refresh on their own. Use Refresh All, or open Query Properties and tick the option to refresh data when opening the file.

    Takeaways

    • One folder, one consistent layout, one query.
    • Clean the sample file once and every file gets the same treatment.
    • Name files with the year and month first, and keep Source.Name for traceability.
    • Exclude temporary files and keep exactly one file per period.

    Still copying and pasting every month?

    We build refreshable Power Query reporting that pulls your monthly files together without the manual steps. Start with a free 30 minute strategy session.

    Book a free strategy session