a university's carbon footprint, moved off one person's spreadsheet.

When he first got in touch, the university’s whole Scope 3 carbon footprint reporting was, in practice, one person. He owned the number: the supply-chain spend, the business travel, the waste and water, all of it worked out to a method he’d built up over years. He knew that job cold - the self-taught part was Power BI and Excel. The reporting itself ran entirely in Excel: several sprawling working spreadsheets, plus a set of corrections he applied by hand every year because the finance system kept miscoding IT equipment under a catch-all line. A single faculty report could mean days of Excel, and there was one to do for every faculty - and that was before the annual whole-university figure or anything ad hoc. The full cycle ran to about eight days by hand.

Power BI was the thing they suspected might help. The ask that reached me was help getting to a dashboard. Fair enough - a dashboard was the visible thing missing. But underneath it sat a bigger question: where would the dashboard’s numbers come from?

The university’s carbon reporting isn’t one report. There’s the annual figure, the faculty briefings, the year-on-year commentary, business travel broken down by mode and cabin class. A dashboard built for any one of those would answer that one and nothing else, and every new question would land back on him.

So the strategy I laid out was one data model underneath all of it: built once, from the data he already had and the logic already spread across his spreadsheets, so that every report - the ones he runs now and the ones nobody’s asked for yet - draws on the same numbers, worked out the same way, from one auditable place. I put the recommendation in writing, and the decision was theirs.

They’d already had a go, sensibly. Summer students had built them some Power BI reports - each one, in effect, a single spreadsheet read in with charts drawn on top. After I’d laid out the strategy, another student took a run at the data model itself, with very little Power BI behind them, and that run settled it: putting charts on data you already have is the straightforward end of Power BI, and a model that stays consistent and auditable across every reporting lens is close to the last thing you learn. Most of my work is done with the team. This piece I did for them - a foundation is worth getting right first time - with the handover built in so it wouldn’t stay mine.

They chose the model, and a good part of the build turned out to be getting the method out of his head and his spreadsheets and writing it down as the model. Water worked out from metered cubic metres rather than estimated from spend; waste from actual tonnage; the taxi calculation that works backwards from cost to distance. Each of those was a correction he’d been applying by hand every year; now it’s just how the model works.

The model itself does the heavy lifting as the data loads, before the reports ever see it. What the university buys, and how it travels, got their own tables, because they’re measured differently and use different emissions methods. The carbon is worked out on the way in, so the reports just add up a column and stay fast.

A few decisions in the build do most of the work of keeping the number trustworthy:

It validated against his own spreadsheet to within rounding.

The point of all this was that he could run it. So the handover was two unhurried sessions with him driving on his own laptop - even on a machine that took a couple of minutes to open the model and kept freezing - because muscle memory only forms if your hands are on the keys. We went through Power Query versus DAX, publishing, splitting the model from the report, and the once-a-year refresh. He’d had a gap since his last training, so we started gently. By the end he could state the split back to me himself: Power Query transforms the data on the way in, DAX does the work inside the model.

The work is finished and handed over, and what’s left behind is his. The once-a-year refresh now takes him under half a day: collect the raw exports, make a few hand corrections, and the model does the rest. The whole method - every calculation the footprint depends on - is in a reference document, and the code that ingests and transforms the data is written to be read: well commented, named so it explains itself, because the point was always that the next person could follow it. And because the model and the reports are separate, the faculty view, the whole-university view and whatever comes next all read from one model; he can point a new report at it himself. He’s since come back for a follow-on piece of work: the next kind of carbon reporting on the university’s list.

This is the sort of work I do embedded with a team, month to month. what working together looks like - shape, rhythm, price ▸