Lorenzo Reale

Power BI consultant · UK and EU

Work

Demonstration project

Data Reconciliation Monitor

A morning check that tells the business which data it can use today, and gives the data team the evidence behind every answer.

  • Client Thornfield Retail Group (fictional)
  • Audience The business each morning, the data team and its lead
  • Sources Warehouse monitoring tables in SQL Server, the issue log from the ticketing tool
  • Built with Power BI · SQL · Power Query · DAX
Status page: areas usable, areas not ready, open issues and the last file in, the status of each data area with the night in figures, and a grid of the last 14 nights
The morning of 30 October 2026: seven of eight areas usable, supplier invoices held back, and the last 14 nights for every area.

Synthetic data · fictional client · model, DAX and data generator on GitHub.

01

The problem

Thornfield's warehouse is loaded every night from six source systems into eight data areas that finance, trading and supply chain report from. When a load failed, the business found out from a wrong figure in a meeting. The data team needed one page to say, before the reports were opened, which areas could be used and which reports to hold back.

02

What it showed

For the load of Friday 30 October 2026, the Status page gives the answer in one board, and any of the last 90 nights can be selected to see that morning as it was.

  • Seven of eight areas can be usedSupplier invoices are not ready: one of three files did not arrive, so 1,204 invoices worth £214,608 are missing and the last file landed at 05:41, 41 minutes late. Accounts payable and supplier spend reporting waits for the resend.
  • Supplier invoices are the recurring problemThey had trouble on 5 of the last 14 nights and hold 5 of the 14 open issues, mostly late or incomplete files from the supplier. Online orders are 3 orders (£412) short for the third night running, inside tolerance and traced to refunds posted after the extract closes, so they are fine for trends.
  • Issues are resolved on time, but the queue is growingIssues closed in the last four weeks took 2.8 days on average against a 3-day target, yet 14 are open against 2 four weeks earlier. The backlog is what the data team lead needs to raise.
Reconciliation page: rows and value at the source and in the warehouse for each area, rows matched by night against the limit, arrival against 05:00, and the data notes
Source against warehouse for every area, the worst area night by night against the 99.5% limit, arrival against 05:00, and what was corrected in the data.
Issues page: open and critical issues, the oldest open issue and days to resolve, the open issue register, the queue by night, and open issues by owner and age
The queue as it stood that morning: open issues by severity, the open queue rising through the month, and open issues by owner and age.
03

How it's built

SQL Server viewsIssue log exportPower QuerySemantic modelReport
  • No status is storedThe load records what it moved: rows and value at the source and in the warehouse, files received and when, and records failing each of 48 rules. One measure compares those figures with the limits in the rulebook, so a tolerance changes in one place and every status follows.
  • Only the load checks decide whether an area can be usedRows and value against the source and files in by 05:00 set the status. The other quality rules add notes for the data team and never hold a report back.
  • Any past morning reads as it wasTrend charts use their own night axis, so they always end on the night selected, and the issue measures compare the dates opened and resolved with that night, so the queue shows what was open at the time.
  • Real problems, handled in the openThe data carries the problems real monitoring tables do: one night's store sales checks written twice by a rerun, arrival times recorded in three formats, nights with no load scheduled, areas with no money to reconcile, a rule added part-way through the window and an issue closed before it was opened. The Reconciliation page counts each one.

Every figure on this page was recalculated from the source files outside Power BI, and matches the report.

04

The source

The semantic model, the DAX and Power Query, the report pages and the script that writes the data are on GitHub, all readable as plain text. The report itself, with its data, is a .pbix file in the project's release, with a PDF of every page.

All data in this project is synthetic. Thornfield Retail Group is a fictional client created to demonstrate the work, and no real client data appears anywhere in it.

Next project Site Inspection Performance

Get in touch

Can your business trust this morning's numbers?

If people find out about a failed load from a wrong figure in a meeting, I'd like to hear about it.

Discuss a project