How the
work was done

The ask. Management wanted one screen showing what equipment the mission had, where it was deployed, and what had changed. Reasonable request. The problem was that the answer lived in nine different units' spreadsheets, none of which agreed with each other.

What was actually wrong

Each unit maintained its own workbook, in its own shape, with its own spelling of the same field office. One list called a site by its town name, another by its sector code, a third by an abbreviation someone invented in 2019. There were no shared keys, so nothing could be joined. Two units tracked overlapping equipment and neither knew it.

Building a dashboard on top of that would have produced a confident-looking screen full of wrong numbers — which is worse than no screen at all.

What I did

• Went unit by unit and worked out what each column actually meant, which is slower and less glamorous than it sounds and was most of the job.
• Designed a three-layer list structure: reference tables (locations, services, asset types, users), inventory tables (one per equipment class), and control tables that drive the dashboard itself.
• Established a single location table as the authority, and rewrote every inventory list to point at it by lookup rather than by typed text. This is the change that made everything else possible.
• Built the Power BI model on those relationships, with a change log so that "what changed since last month" is answerable without asking anyone.

What I'd tell someone starting the same job

The dashboard is the last five per cent. Budget your time for the reconciliation conversations — getting two units to accept one spelling of a location name is a negotiation, not a data task, and it is where the project succeeds or fails.

Also: make the reference tables boring and rigid early. Every week you delay, more rows accumulate that will need rewriting.

The ask. Every field office should report the status of its ICT services each morning, so the operations cell knows what's down before management asks.

The constraint that shaped everything

Twelve field offices, several hundred service checks between them, and connectivity that ranges from adequate to absent. A form that requires a stable connection for four minutes will not get filled in. A form that loses the user's work when the link drops will get filled in once, and then never again.

Design decisions

• Everything defaults to OK. The common case — all services normal — should be one tap and submit. Reporting is only laborious when there's actually something to report.
• Minimum round trips. The service list loads once into a local collection; the user works against that and writes back in a single operation, instead of hitting the server per field.
• Escalation is automatic, not social. A scheduled flow checks who hasn't submitted and sends the reminder. Nobody has to chase colleagues personally, which is what makes daily reporting survive past the first month.
• A "down" service forces a follow-up. Marking something down requires the notification to go out — the report can't quietly record a failure that nobody acts on.

What I got wrong first

The first version asked users to set a status on every service individually, and used a map visual to show location health. Both were mistakes. The per-service interaction made a good day as much work as a bad one, and the map looked impressive while being harder to read at a glance than a plain grid. Replacing the map with a status matrix was an improvement that felt like a downgrade for about a day.

The ask. Reconcile the operations cell's ICT inventory against the organisation's ERP asset extract, so that write-off and handover decisions rest on something defensible.

The shape of the problem

Roughly nineteen thousand asset records, spread across just over four hundred material categories. The two sources disagreed in three distinct ways, and each needed a different response: records in the ERP with no physical counterpart, physical equipment absent from the ERP, and records present in both but describing the item differently.

Method

• Normalised both extracts in Power Query — trimming, case-folding and standardising the identifier columns before any comparison, because most apparent mismatches are formatting.
• Used three joins deliberately rather than one: an inner join for genuine matches, and left-anti and right-anti joins to isolate each kind of orphan separately. Keeping the three populations apart is what makes the output actionable — they go to different people for different decisions.
• Traced a row-count inflation that turned out to be duplicate description values in a lookup table — a reminder to check cardinality on every relationship before trusting a total.
• Handed each population to the unit that could act on it, with the evidence attached.

What made it stick

The reconciliation was run jointly with the asset management unit rather than delivered to them. A spreadsheet of discrepancies sent by email gets ignored; a discrepancy list that two units built together gets cleared. In one exercise across six field offices, 2,723 assets were reviewed and 262 items resolved — 125 serviceable items handed over and 137 initiated for write-off.