Compliance by site
% of items in date (Compliant + Due Soon) against the 95% target
Status by compliance category
Share of items in each status
Inspection pipeline · next 12 months
Items falling due each month, with today's overdue backlog
Status mix
All items at this site
Items by category
Stacked by status
Compliance register
Worst first. Location is shown for block-level items only.
Compliance heatmap · site × category
% in date. Blank means the category does not apply to the site.
How long overdue
Overdue items by age band
Overdue action list
Sorted by days overdue. Safety-critical items flagged.
What the clean-up changed. The spreadsheets reported items as overdue. of those had placeholder dates from 2001–2002, meaning no inspection was ever recorded. The model reports them as No Record so they can be chased separately from genuinely late inspections.
Spreadsheet status vs model status
Item counts before and after the status rule was applied
Record completeness by site
% of items with a valid next due date and % verified
Transformation steps applied in Power Query
Run on every refresh, so new spreadsheets are cleaned the same way
- Combine 16 workbooks from one folder. Folder.Files → Excel.Workbook → Compliance sheet. Site name taken from the file name.16 files
- Standardise item names. Keyword mapping table groups free-text names into standard compliance items and categories.204 → 41 items
- Replace placeholder dates. Any date before 2010 set to null, so the item is reported as No Record.
- Recalculate status. Overdue / Due Soon (≤60 days) / Compliant from Next Due against today.419 rows
- Extract frequency. Annual, 5-yearly, monthly etc. parsed from the item text into months.freq column
- Anonymise. Supply numbers, supplier and contractor names removed; block references generalised.PII-free
- Join to site dimension. Site reference, address and region added from DimSite.16 sites
Star schema
One fact table, five dimensions, single-direction one-to-many relationships
FactCompliance
- SiteKey · ItemKey
- DueDateKey · ContractorKey
- LastInspection
- NextDue
- FrequencyMonths
- Verified
- Status (calc)
- DaysToDue (calc)
DimSite
- SiteKey
- Site · SiteRef
- Address · Town
- Region
DimItem
- ItemKey
- StandardItem
- CategoryKey
- SafetyCritical
- DefaultFrequency
DimCategory
- CategoryKey
- Category
- SortOrder
DimDate
- DateKey
- Date · Month · Year
- FY · IsPast
DimContractor
- ContractorKey
- ContractorType
Key DAX measures
Measures folder: _Measures
Power Query (M) · fxCleanRegister
Applied to each workbook in the folder