How to Automate Your Monthly Excel Reports With Power Query

1. Define the report and confirm your Excel setup

Write down the decision the report should support, its reporting period and its source. For a simple example, use monthly sales exports with InvoiceID, InvoiceDate, Customer, Status and NetAmount columns. Aim for a monthly net-sales total and a breakdown by customer, excluding void invoices under a rule you explicitly document. This example is a reporting exercise, not an accounting review.

Choose a single currency and document whether the amount includes tax, refunds and adjustments. Decide how dates, time zones and cancellations are treated. If the source exports invoice lines rather than one row per invoice, record that grain before building totals or duplicate checks.

The steps below describe desktop Excel with Data > Get Data and a From Folder connector available, commonly Excel for Windows. Power Query features and supported connectors vary by Excel version and platform. Check your actual installation first; Mac and web workflows may differ. This guide does not require buying another product or adding an integration.

2. Prepare a clean folder of consistent exports

Keep untouched source exports in a dedicated input folder and store the reporting workbook elsewhere. For example, use Sales_2026-08.csv and Sales_2026-09.csv with the same columns, date format and delimiter. The folder should contain only approved inputs, not downloaded duplicates, output workbooks, temporary files or unrelated exports.

Decide whether exports contain only new monthly transactions or a cumulative history. Appending January-through-March history to January-through-February history duplicates prior transactions. Either keep one authoritative cumulative file or use disjoint monthly exports. Do not fix overlapping data by removing duplicates until you know the correct unique business key.

For Excel inputs, use a consistent named table or worksheet and header row in every file. Avoid merged cells and report-style title blocks. Request redacted sample files when discussing help; customer names, bank details and other sensitive data are not needed to start a scope review.

3. Import and combine the files

Start with two known-good periods so you can test both the template and the combination. Combining files uses a sample file to define transformations, which are then applied to the other files. Matching filenames alone do not prove matching structure.

1. Open a blank workbook. For one file, choose Data > Get Data > From File > From Text/CSV or From Workbook, then Transform Data. For recurring same-format files in a folder, choose From File > From Folder and select the input folder.
2. Choose Transform Data to inspect the file list. Filter Extension and Folder Path so only the intended CSV or Excel files are included. Exclude hidden and temporary files and review whether subfolders are included. Keep the source filename available for tracing unexpected rows.
3. Use the Content column's Combine Files action (or Combine & Transform Data). Select a representative sample. For CSV, confirm delimiter and encoding; for Excel, select the same named table or sheet from each workbook.
4. Review the generated sample-file and combined queries. Apply common header and cleanup steps in the sample transformation when they must run for every file. Give queries readable names, such as SalesFiles and CleanSales, instead of relying on default names.

4. Save explicit cleanup rules

Power Query records Applied Steps rather than requiring you to repeat edits on the worksheet. Work in small steps and check the preview after each one. Automatic type detection is a starting guess, not proof that a date or number was interpreted correctly.

1. Remove only known title rows, promote the actual header row, and remove repeated header and footer-total rows using an explicit rule. Do not blindly delete a fixed number of rows if the export layout varies.
2. Keep identifiers such as InvoiceID as Text to preserve leading zeros. Trim surrounding whitespace in customer and status fields. Standardize agreed status values before filtering; keep an exception list for unexpected values instead of discarding them silently.
3. Set InvoiceDate to Date and NetAmount to an appropriate numeric type. Use Change Type > Using Locale where the source date order or decimal separator differs from your computer. Check negative amounts, blank dates and errors. Do not replace all conversion errors with zero: investigate and report them.
4. Filter to the defined reporting population, for example excluding Status = Void while retaining valid credit adjustments. Confirm that this matches the source report's filters. Deduplicate only against an agreed unique key; repeated invoice IDs may be valid line items.
5. Add a Month column by selecting InvoiceDate, then Add Column > Date > Month > Start of Month. A real month-start date sorts across years more reliably than a month name. Keep the original date and source filename for reconciliation.

5. Load a refreshable report and establish control totals

Choose Home > Close & Load To and load the cleaned query to an Excel table. Build a PivotTable from that table with Month in Rows and Sum of NetAmount in Values. Add Customer as another row field or filter if useful. Keep calculated summaries separate from imported data: typing corrections into query output is unreliable because refresh can replace them.

Create a control sheet that records expected source totals by period, imported row counts, query errors and excluded rows. You can reference the clean query and use Group By to create month totals, but compare those totals to an independent source export or source-system report. Two summaries generated from the same incorrect query are not independent verification.

For a fictional check, if August's approved source total is 12,450.00 and September's is 13,125.00 in the same currency and basis, the combined total should be 25,575.00. Confirm each month as well as the grand total. A correct grand total can hide an amount assigned to the wrong month. Record any intentional rounding tolerance and investigate differences outside it.

Reconcile filtered and rejected rows back to the raw export. Check distinct invoice counts only when that measure matches the source grain. Look at several individual records, including a refund, a month-end date and a leading-zero ID. This validates the transformation rules; it does not certify the accounts.

6. Follow the same monthly refresh checklist

Refresh repeats transformations against files available at that time. It does not fetch a new export from your business software. A folder query normally reads all files that still match its filters, so moving old files out of the folder can also remove historical rows on the next refresh.

1. Save a versioned backup of the workbook and approved report. Export the new period using the documented filters and place the file in the input folder once. Keep an input register so the same period is not imported twice.
2. In Excel select Data > Refresh All. Wait until the queries finish, review Queries & Connections for failures, then refresh the PivotTable if needed after the data load completes. Do not assume a displayed table is current merely because it contains last month's data.
3. Run the control-sheet checks: expected periods, source filenames, row counts, blank keys, conversion errors, excluded rows and totals against the source. Investigate mismatches before sharing the report.
4. Save the workbook and approved output with the report period and a verified refresh time. Document the folder path, query names, filters and checks so another person can repeat the process. If the folder moves, update the source path through Data Source Settings or the Source step and retest.

7. Treat export layout changes as a new input contract

A renamed Amount column, a new summary footer, a different worksheet name or a change from day/month to month/day can break refresh or produce plausible but wrong numbers. Watch the sample query and every Applied Step after a source-system update. New columns may be ignored by an existing expansion step until you explicitly include them.

Keep one accepted sample per export version. Test a changed file in a copy of the workbook, inspect errors and rerun the full reconciliation. If formats are genuinely different, use separate transformation queries and append standardized outputs only after columns, types, currency and row grain match.

When refresh fails, keep the last approved report clearly marked with its period. Do not distribute stale data as current or remove error-producing rows just to get a green refresh. Restore the last working workbook and input set if needed, then investigate the changed step. Preserve originals so the change can be reviewed.

8. Know what file-based automation does and does not do

This workflow automates cleanup, combination and summary preparation after you supply files and trigger refresh. Refresh-on-open options still depend on the workbook environment and access to the source files. They are not an unattended, scheduled integration that guarantees a report while the computer is off.

Live QuickBooks connections, API authentication, scheduled cloud jobs and automatic delivery are separate projects with different permissions, maintenance and sometimes licensing requirements. They are not part of this DIY method or the starting offer below. Agree the file format, report output, validation rules and handover steps before hiring someone to automate it.