Power BI Data Reconciliation for Safety Reporting

Power BI can make inconsistent data look polished. Reconciliation is the control that proves the model is complete, correctly transformed and aligned with agreed business definitions.

Start with source control totals

For each reporting period, capture expected totals from the source systems: event counts, hours, workforce numbers, actions, audits or training records. Compare those values with the model after extraction and transformation.

Reconcile the reporting population

Many reporting errors come from filtering rather than arithmetic. Confirm which sites, departments, contractors, event types and date ranges are included. A technically correct measure can still be wrong if the reporting population is not what the business expects.

Check unique identifiers

Use stable keys for incidents, people, contractors, actions and other entities. Duplicate IDs or inconsistent joins can multiply records and inflate totals. Where a source lacks a reliable key, define a controlled matching approach and monitor exceptions.

Validate date logic

Safety data can contain reported date, event date, investigation date, action due date and closure date. Make sure every visual uses the intended date relationship. Monthly reporting often breaks because one page uses event date while another uses created date.

Reconcile hours separately

Hours can come from payroll, time and attendance, contractor systems or manual files. Compare monthly totals to source reports, identify unexplained variance and document how missing or late hours are handled before calculating frequency rates.

Check hierarchy and contractor mapping

Department and contractor names change over time. Create mapping rules for historical reporting and monitor unmapped values. A single spelling variation can split the same organisation across multiple chart categories.

Build exception tables

Do not hide unmatched or invalid records. Create visible exception tables for missing hierarchy, unknown categories, duplicate IDs, failed imports and out-of-range dates. This turns data quality into an operational process rather than a one-off cleanup.

Use control measures in Power BI

Simple validation measures can compare loaded records to expected totals and highlight variance. These controls are especially useful after scheduled refreshes or file replacements, where a source extract can change without anyone noticing.

Document business definitions

Keep a metric dictionary covering event classifications, recordability, hours population, organisational attribution, rolling periods and exclusions. Reconciliation becomes much easier when the expected answer is formally defined.

CloudHub provides Power BI consulting and managed reporting across source integration, data models, DAX, validation and publishing.