Reporting
Automate Report Data Prep
A report can refresh successfully while its source rows are still wrong. Dates may use different formats, customer names may differ between files, or a join may multiply the totals. A repeatable preparation step makes those problems visible before the reporting table is released.
Key takeaways
Define a unique key and the meaning of one row.
Keep raw extracts separate from transformed data.
Validate dates, types and lookup uniqueness.
Automate report data prep by preserving the incoming files, applying agreed field and type rules, matching records on stable keys, and reconciling the output. Publish a dated prepared table only after its checks pass. Keep rejected records and failure reasons for the person responsible for fixing them.
- Capture complete inputs
- Normalize fields
- Match and validate
- Reconcile rows and totals
- Release the prepared table
Define what one row represents
Before connecting tools, write one sentence describing a row in the prepared table. For example: one row is one completed service job. Decide which identifier makes that row unique. A customer can have many jobs, so a customer ID alone would be the wrong uniqueness check.
List required columns, accepted types and allowed blank values. Record the meaning of dates and amounts. Keep currency codes when more than one currency is present. An empty amount is not automatically zero, and a text identifier with leading zeros should remain text.
Keep raw inputs separate from prepared output
Save each incoming extract with its source, capture time and run identifier. Apply transformations to a working copy. This gives you evidence to investigate a correction without asking someone to recreate an old export. Set retention and access according to the sensitivity of the source.
Check that the expected files arrived and that an export is complete. A smaller file may be a legitimate quiet day or an interrupted extraction. Use source completion signals and agreed thresholds to distinguish them. Avoid publishing a partial table because the first file happened to arrive early.
Standardize fields with explicit rules
Map each source column to the agreed output column. Convert dates using the known source format and locale. Reject ambiguous values such as 03/04 when the source convention is unknown. Record the original value alongside the reason it needs review.
Use a controlled lookup for statuses and categories. For example, Done and Completed can map to Completed if the business confirms they mean the same thing. An unfamiliar status should remain visible in an exception list. Automatically treating every new value as the nearest familiar word can hide a process change.
Power Query supports repeatable preparation steps, including merge, append and type transformations. It can be useful when the team already works in Excel or Power BI. Refresh and connection options depend on the host product, so confirm how the chosen environment will run and store the result.
Check joins before calculating totals
A join adds related information to your main rows. If you join jobs to a customer lookup, the lookup should normally contain one matching row per customer ID. Two matching lookup rows can double a job in the output and inflate its amount. Check lookup uniqueness before performing the join.
In an illustrative batch, 120 source rows contain five exact repeat records and two invalid dates. There are 113 valid unique job rows. Six have customer IDs absent from the lookup. The workflow holds those six for review and releases 107 matched rows. Its reconciliation is 120 = 5 repeats + 2 invalid + 6 unmatched + 107 released.
Count records as well as checking totals. If the 113 valid jobs total 8,400 in one currency, the released and held amounts should reconcile to 8,400. A mismatch needs investigation. Never sum different currencies without a separately agreed conversion method and dated rates.
Release one checked snapshot
Write the candidate table to a separate destination or version. Run the required checks before making it the current reporting input. Keep the last successful snapshot clearly dated if the new run fails. A dashboard should show the data’s actual freshness instead of presenting an old table as today’s result.
Use a run ID and source fingerprint to recognize a repeated extraction. A rerun should reproduce or replace the same candidate snapshot according to the agreed rule. It should not append the whole batch again. Keep manual corrections in a controlled mapping or correction table so the next refresh can apply them consistently.
Test changes as well as ordinary files
Try a normal batch, empty source, renamed column, duplicate lookup key, invalid date and missing customer ID. Include a failure after preparation but before release. Confirm that readers still see the last accepted version and that the operator receives enough detail to resolve the failed run.
Start with one report’s input table. Compare its rows and totals with a trusted manual preparation across several cycles. Track rejected rows and correction time as well as run success. Those measures reveal whether the workflow is reducing checking effort.
Bring Automations For Business a redacted source sample and the table your report expects. We can build custom preparation rules around your current tools, including the exception list and the approval needed to release a corrected snapshot.
Your ready-to-use checklist
0 of 6 checked
Make the next step clear.
Automate Report Data Prep planning brief
Free templateConnect this with the rest of your workflow
Sources and notes
Sources checked on October 29, 2026.
- Microsoft: what Power Query does
Repeatable data transformations and differences between host products.
