Rebuilding the same report every week or month: how to make it repeatable and checkable

Why hand-built reports drift

Each period someone opens the export, filters, copies, pastes and adjusts. A filter is missed, a range stops one row short, a date boundary moves a day or a pasted value replaces a formula. Only the finished pack is looked at, so the errors reach readers. When the one person who knows the steps is away, the report either stops or changes.

Write the export contract and the period rule

A contract says what the export must look like every period: the column names and their order, the type of each column, the date format and how the period is defined. For example: all records dated from the first to the last day of the month, inclusive, by the order date. Add the first and last record dates and the row count you expect, so a short or duplicated export is noticed before it is used.

Anything that is a business definition, such as what counts as a completed order, belongs in the contract and is changed only by the person who owns the definition.

• Column names, order and types.
• Date format and period boundaries.
• Definitions of each reported figure.
• Expected row count and first and last record dates.

Build so that refreshing changes only the data

Keep the data and the report on separate sheets. Microsoft says an Excel table as a PivotTable source includes rows added later when the PivotTable is refreshed, that a PivotTable works from a snapshot and that it must be refreshed when the source changes. Without a table, you must change the source range yourself, which is exactly the step people forget.

If each period arrives as a separate file in a folder, Power Query can combine files that have the same file type and structure, including the same columns. It analyses an example file and applies the same steps to every file. That is the reason the export contract matters: the combination assumes every file has the same columns, so a changed column should be caught by a check and not absorbed into the report.

Refreshing has its own cautions. Microsoft notes that Power Query keeps a local cache which is not refreshed automatically, and a yellow bar can warn that a preview may be days old. Check that the report really used this period's data.

Control totals and a note for every pack

For each period, compute a few totals directly from the export, not from the report, and compare them with the same totals in the pack: row count, sum of the main amount, count of distinct categories and the first and last record dates. Record the result in a short note, together with anything unusual, such as a new category or a missing day. The note is what lets a reader trust the pack without redoing it.

What to do when the export changes, and where the paid job fits

When columns are renamed, a new category appears or the date format changes, stop. Report what changed and decide the rule; do not let the report absorb it. The rules and the rule version used should be on the note.

The standing service builds the pack from your export each period by your written rules and checks it with control totals, from £295 a month for one monthly pack from one export (an untested proposal, quoted by proposal), with the first month used to set up the layout and rules. Weekly packs are quoted separately, and the service starts only after a secure way to send each period's export has been agreed in writing; no upload portal exists yet. It does not connect to live systems, send the pack, change your definitions or give financial advice, and it is not a guarantee of delivery time. The first enquiry needs a description of the report and the export's column headings, never the data.