Phase 1 of the monthly report build: Excel to Power BI

Before: a workbook filled in by hand

The monthly report was an 11-tab Excel workbook with roughly 700 manually maintained input cells, 17 tables and one chart. Every input cell was a number someone had to find in Procore or the accounting system and key in by hand. The data was a copy of a copy, the workbook accepted wrong values without complaint, and the scoring logic lived in cell formulas nobody could test.

What we found: two errors that cancelled out

Rebuilding the scoring logic meant reading every formula in the workbook. Two were wrong. Schedule Performance compared values against an incompatible scale, so it fell through to an incorrect default score. Completion Variance did not match any defined scoring band, so it returned zero. On the sample project the two errors offset each other, so the total looked right, which is why nobody had caught it. Roughly 42% of the scorecard weighting was disconnected from actual project performance.

What we built: Procore to Fabric to Power BI

One Fabric notebook authenticates against the Procore API and extracts every endpoint the report needs, from budgets, commitments and billings to submittals, RFIs, punch items, incidents, vendors and insurance. Raw payloads land in bronze with audit columns. Silver types and validates the data and logs rejected rows with a reason instead of dropping them; Sage 100 Contractor AR and AP join through read-only SQL. Gold maps every Procore, Sage and Outbuild record to one project with normalized dates, and 63 data-quality expectations gate the data before Power BI reads it through Direct Lake. Fields no system holds, like project update notes, are captured through a validated SharePoint page or a CSV template. The semantic model is a star schema with 99 measures behind 12 report pages.

What changed

The report now comes from the source systems with checks on every run, instead of being rekeyed each month. Scoring logic is explicit and testable rather than hidden in cells. A source coverage page shows which records are present, missing, delayed or unmatched, so a project missing from accounting no longer passes for one with $0 of activity, and the scorecard states its own data coverage: 59% in the walkthrough, with Procore and Sage connected. Adding a source means adding an endpoint to the extraction notebook, not starting over.

Scope

One monthly report: an 11-tab Excel workbook rebuilt as a bronze, silver and gold lakehouse pipeline, a 99-measure semantic model and a 12-page Power BI report, with a data-quality gate on every load.

Watch the walkthrough

Video unavailable? Watch on YouTube or read the written breakdown.