Service
Reporting automation
When the monthly report feels like a small construction project every time, it is rarely missing Excel skills, it is a process that demands too much manual work.
The problem
What I usually find: one central reporting workbook with 20-40 sheets. Data from three to five sources (SAP, Salesforce, a CRM export, hand-typed entries, an old Access database) is copy-pasted in, joined with VLOOKUP and INDEX/MATCH, run through twelve pivot tables. End of month, one person spends two days building, a second spends three days checking.
The deeper problem is not the effort. It is that nobody can fully explain how a given KPI is computed. When controlling asks why margin Region North drifted 0.4%, the answer is not in the file. It is in one person's Excel history.
Worse: every source-system change (new column, changed codification) breaks the file silently. Sometimes nobody notices for three months.
The solution
I rebuild your reporting pipeline as clean layers: Power Query for import and cleansing (connectors, type detection, joins, validation steps), a reproducible data model (star schema in PQ or Power Pivot/DAX depending on Excel version), and a thin VBA layer only for what Excel cannot do natively (formatted PDF export, Outlook send, branch selection). For larger estates we migrate the model to Power BI or a thin SQL Server stage between source and Excel.
KPIs are documented in a definitions table: each KPI with formula, source, owner, refresh frequency. Power Query gets validation steps that fail loudly when a source structurally changes, instead of silently producing garbage. Refresh emits an audit log with timestamp, source hashes, and a diff summary of key figures.
Dashboards get what decision-makers actually need: tabular numerals, clear KPI order, prior-year and prior-month comparison as default, no loud donut charts. For multilingual group reports, a language switch that flips the whole layout consistently.
Typical use cases
- Monthly or weekly reporting with 3-7 sources, Power Query as ETL, Excel/Power Pivot or Power BI as model, automated PDF distribution
- Migrating a grown Excel report to a SQL Server stage (views, stored procedures) when Excel hits performance limits
- Group reporting with subsidiary consolidation, typed contracts and eliminations, GDPR-compliant data flow
- KPI-definitions layer: every metric documented with formula, source, owner, in an auditable table
- Data quality monitoring: automatic plausibility checks (ranges, distributions, prior-year ratios) with clear error markers before send
- Migrating a hand-built board report to Power BI with row-level security and automated refresh
Concrete benefits
- Reporting build time reduced from 1-3 days/month to 15-30 minutes
- Full reproducibility: every step from source pull to PDF is documented in Power Query / VBA / DAX and versionable
- Audit trail: every refresh emits a log with timestamp, source versions, delta summary. Auditor-fit.
- Early-warning validation: source changes break the pipeline with clear errors instead of silently delivering wrong numbers
- Model clarity: every KPI has a single definition, owner, documented formula
- Handoverability: a second analyst takes over after 2-3 days, not 6 months
How we work together
Discovery + data-flow mapping
I walk through the current report build with you, document every source, step, assumption. Output is a flow diagram and an initial risk list with the sharpest pain points.
Architecture decision
Power Query in Excel? Power Pivot with DAX? Power BI Service? SQL stage between source and Excel? Decision based on data volume, audience, licenses, GDPR. Trade-offs in writing.
Iterative implementation in 1-2-week sprints
Sources connected one at a time. Each sprint delivers something runnable, checked against the old manual report. Diffs resolved before moving to the next source.
Validation over 1-2 month cycles
The new report runs in parallel with the old. Diffs are explained or fixed. Only when both reports match for two consecutive months do we turn off the old one.
Handover with docs, KPI glossary, training
Step-by-step monthly refresh guide, glossary of all KPIs with formula and source, 60-90 min training with recording. Optional retainer for extensions.
Frequently asked questions
Power Query, VBA, Power BI, or SQL: what do you recommend?
No blanket answer. Rule of thumb: Power Query for import and transformation, Power Pivot/DAX for model and KPIs, VBA only for Excel-specific output (formatted PDF, Outlook). Power BI when you need visualizations in browser or tablet anyway. SQL stage when data volume exceeds Excel or multiple reports share sources. We decide in the discovery workshop.
Do you replace our BI tool, data warehouse, or ERP?
No, expressly not. I build reporting pipelines on top of your existing infrastructure. If I see a need for a DWH or proper BI tool, I will say so and you bring in a DWH specialist. My focus is the layer between source and Excel, sometimes with Power BI on top.
We have an 80MB Excel reporting file with three Power Query queries each taking 20 minutes. Can you make it faster?
Almost always yes. 20-minute PQ queries are usually query-folding problems (steps preventing PQ from delegating work to the source), Cartesian joins, or inefficient pivot/unpivot. Typical improvement: 5-15x. If the data volume is fundamentally too large for Excel, I will say so and propose SQL stage or Power BI.
How do we prevent the report from becoming messy again?
Three things: a KPI glossary with owner per metric, strictly named Power Query steps (no 'Changed Type1, Changed Type2' wilderness), and a README with extension conventions. Optional quarterly review in the retainer.
Can we do this without a Power BI license?
Yes. Power Query, Power Pivot, and DAX are included in Excel 365 without extra licensing. Power BI Service is needed only when you want to share reports on the web, use row-level security, or scheduled refresh outside of Excel. For most mid-market reporting, Excel + Outlook is enough.
How long does a typical reporting project take?
From first discovery to production run with validation: 6-12 weeks for a mid-size monthly report with 3-5 sources. First increments runnable in 2-3 weeks. Fixed-price packages possible after the audit.
Related services
Audit your reporting pipeline?
Send an anonymized version of your current report or briefly describe sources and the monthly routine. Within two working days you get a read: where the largest levers are, what it realistically costs, in what sequence I would proceed.