Automate a Monthly Excel Report with Power Query

Published November 9, 2026

Most recurring Excel reports follow the same pattern. Someone exports a file from the accounting system, CRM, or warehouse tool, pastes it under last month's data, fixes the dates that came through as text, deletes the blank rows, and refreshes the PivotTable. The steps never change, which is exactly why they are worth automating.

Power Query is built into Excel for Windows and is the most direct way to do this without writing macros. You record the cleanup once as a series of steps, and every refresh replays those steps on whatever files are in the source folder. This guide walks through the full setup for a monthly or weekly report.

What does an automated Excel report actually look like?

The finished workbook has three layers:

A source folder. Every period, the new export is saved into one folder, for example Sales Exports. Nothing else goes in there.
A Power Query query. It reads every file in the folder, stacks them into one table, and applies your cleanup rules.
An output. The query loads to an Excel table, a PivotTable, or the Data Model, and your charts and summary sheets point at that output.

Next month, you save the new export into the folder, open the workbook, and select Data > Refresh All. No pasting, no reformatting.

Before you start: get the source files in order

Power Query is forgiving about messy data, but it needs consistency between files. Check these before building anything:

Same layout every time. Column names should match across exports. If the system sometimes adds or renames a column, note it now; it is the most common reason a refresh fails later.
One file type. Keep the folder to CSV only, or Excel workbooks only. Mixing them complicates the combine step.
Predictable names. A name like sales_2026-10.csv lets you extract the period from the file name if the data itself has no date column.
A stable location. Use a path that will not change, such as a shared drive or a synced SharePoint or OneDrive folder. Moving the folder later means updating the query.

Step 1: Connect Power Query to the folder

In Excel for Windows, go to Data > Get Data > From File > From Folder, browse to the folder, and select Open. Excel shows a list of the files it found, including any in subfolders.

You get three choices. Combine & Transform Data is the one you want: it opens the Power Query Editor with the files already stacked, using one file as the example for how to read the rest. Microsoft's documentation notes that every file is processed the same way as that example file, which is why consistent layouts matter.

If the folder contains files you do not want, such as an old template or a readme, choose Transform Data first, filter the file list, and then combine.

Step 2: Clean the data in the Power Query Editor

Every action you take in the editor is recorded as a step in the Applied Steps pane. Typical cleanup for an exported report:

Set data types. Make dates real dates and amounts real numbers. Power Query guesses types, but its guesses are based on a sample, so confirm them.
Trim and clean text. Exports often carry trailing spaces or non-printing characters that make "Acme Corp" and "Acme Corp " count as two customers.
Remove blank and total rows. Filter out rows where a key column is empty, and remove any subtotal rows the source system adds.
Standardize labels. Replace inconsistent values, such as "N. America" and "North America", so groupings stay correct.
Keep the file name. The combined result can include the source file name as a column, which is useful for tracing a bad number back to its export.

What does the Power Query M code look like?

The editor writes M code for you, but it helps to see a clean version. The query below reads every CSV in a folder, keeps the source file name, sets types using US formatting, cleans the text columns, and drops incomplete rows. You can paste it into Home > Advanced Editor and adjust the path and column names.

let
    Source = Folder.Files("C:\Reports\Sales Exports"),
    CsvFiles = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv"),
    WithData = Table.AddColumn(
        CsvFiles,
        "Data",
        each Table.PromoteHeaders(
            Csv.Document([Content], [Delimiter = ",", Encoding = 65001]),
            [PromoteAllScalars = true]
        )
    ),
    KeepColumns = Table.SelectColumns(WithData, {"Name", "Data"}),
    Expanded = Table.ExpandTableColumn(
        KeepColumns,
        "Data",
        {"Order Date", "Region", "Customer", "Amount"}
    ),
    Typed = Table.TransformColumnTypes(
        Expanded,
        {
            {"Order Date", type date},
            {"Region", type text},
            {"Customer", type text},
            {"Amount", type number}
        },
        "en-US"
    ),
    CleanText = Table.TransformColumns(
        Typed,
        {
            {"Customer", each Text.Proper(Text.Trim(Text.Clean(_))), type text},
            {"Region", each Text.Upper(Text.Trim(_)), type text}
        }
    ),
    RemoveIncomplete = Table.SelectRows(
        CleanText,
        each [Amount] <> null and [Customer] <> null and [Customer] <> ""
    ),
    Renamed = Table.RenameColumns(RemoveIncomplete, {{"Name", "Source File"}})
in
    Renamed

Listing the columns explicitly in Table.ExpandTableColumn means an unexpected extra column in a new export is ignored instead of breaking the load. And passing "en-US" to Table.TransformColumnTypes tells Power Query how to read dates and decimals, so the result does not depend on the regional settings of whoever clicks Refresh.

Should the folder path be hard-coded?

For a workbook that more than one person uses, no. Create a parameter with Home > Manage Parameters, store the folder path in it, and reference the parameter in the Source step. When the folder moves, you change one value instead of editing code.

Step 3: Load the result where the report needs it

Select Home > Close & Load To and pick an output:

Table if people will filter the detail rows or other formulas read from it.
PivotTable Report if the report is a summary by month, region, or product.
Only Create Connection plus Add this data to the Data Model if the combined data is large or you want to relate it to other tables, such as a product list or budget.

Build your summary sheets, charts, and PivotTables on top of this output. Avoid typing manual adjustments into the loaded table, because the next refresh overwrites them. Keep manual inputs, such as commentary or one-off corrections, in a separate table that the report reads alongside the query output.

Step 4: Refresh each period

The monthly routine becomes:

Save the new export into the source folder.
Open the workbook.
Select Data > Refresh All.

To remove even step 3, open Data > Queries & Connections, right-click the query, choose Properties, and turn on Refresh data when opening the file. The same dialog has an option to refresh every set number of minutes while the workbook is open.

Before you hand the report over, test it. Add a second month of files, refresh, and confirm the totals match what you would have calculated by hand. Then rename a column in a test copy of an export to see what a failed refresh looks like.

Does this work on Mac and in Excel for the web?

Partly, and the details change as Microsoft adds features. Excel for Mac and Excel for the web both support Power Query, but the available data sources, and whether you can create a query or only refresh one, differ from Excel for Windows. A query pointing at a folder on someone's C: drive cannot be refreshed from a browser, because the browser has no access to that drive. If your team works across platforms, build the query in Excel for Windows, store the source files in SharePoint or OneDrive, and check Microsoft's current "Power Query data sources in Excel versions" page for what each platform supports.

When this approach stops working

Power Query handles a large share of recurring reports well, but it has limits:

Excel does not refresh on its own schedule. Someone, or something, still has to open the workbook. Fully unattended refreshes usually mean moving the logic into Power BI, which offers scheduled refresh in the service, or adding a separate workflow tool.
Source layouts that keep changing. If the export format shifts every few months, each change needs a query update. Fixing the export upstream is often cheaper.
Very large data. A worksheet table has a row limit, and refresh time grows with the number and size of files. The Data Model helps, but at some point a database or Power BI is the better home for the data.
Logic that needs judgment. Power Query applies rules. If each month requires decisions, such as which invoices to exclude and why, those decisions need a clear input table or a person.
Output formatting. Power Query shapes data; it does not format a report pack or send emails. That part needs formulas, VBA, or a workflow tool on top.

If your report is mostly about reshaping and summarizing data for decision-makers, our data analytics and reporting services cover the step up to Power BI. For workbook-level automation, see custom Excel automation, or browse live demos built on synthetic data to see finished examples.

When to bring in a specialist

If your report pulls from several systems, needs to run without anyone opening Excel, or has already broken a few times after source changes, a specialist can design the queries so they survive those changes. You can describe your project and get a fixed quote, and the preliminary price estimator gives a rough range first if you prefer.

How to Automate a Monthly Excel Report with Power Query