Making Sense of Field Service Stock

It started, as these things usually do, with a small annoyance.

You need to answer a simple question. Do I carry that part? Have I used one recently? Is there a pattern to the visits where I turn up and cannot finish?

The information to answer that exists. It just exists in a shape that means answering it takes twenty minutes of copying, sorting and squinting — every single time you want to know.

Doing that once is fine. Doing it every week is a signal.

I did not go looking for a data tool. I went looking for a way to stop repeating myself.

Power Query is genuinely well suited to this, for a reason that has nothing to do with it being clever: it records the steps rather than the result.

You clean the export once — split that column, fix those dates, throw away the rows that are noise, join it to the other sheet, rename the headers into English. Then next month's export drops in and the whole thing runs again.

That is the actual win. Not the analysis, the repeatability. The twenty-minute job becomes a refresh, and because the steps are written down, you can also see what you did, which past-me was not always good about.

It also forces a useful discipline. You cannot hand-fix a value halfway through, because there is no halfway. Every correction has to become a rule. That is mildly irritating for about a week, and then it turns out the rules were the thing you were missing.

Most of the work is not analysis. It is agreeing what things mean.

The same part appears three ways depending on who typed it. Dates arrive in two formats in the same column. A blank cell means "zero" in one sheet and "we did not check" in another. One system's idea of a completed visit is not the same as the next system's.

None of that is a technical problem, and no amount of clever querying fixes it. It is a definitions problem wearing a technical costume, and until you sort it out every chart you build is confidently wrong.

Working out those definitions taught me more about how field service actually operates than the finished output did.

I have become fairly hard-nosed about the output.

If it needs explaining, it has failed. If it has more than a handful of things on it, it has failed. If it answers a question nobody was asking, it has definitely failed.

The useful version is boring and short. What moves. What does not. What runs out. What causes a second visit. Anything beyond that is a chart made because charts are satisfying to make.

The same instinct applies here as on a machine. A dashboard with forty readouts and no hierarchy is not more informative than one with three. It is just louder.

Ongoing, and deliberately unfinished.

The repeatable cleaning works, which was the original point. The definitions are better than they were and still not right. The outputs keep getting shorter, which I have decided to read as progress.

Push it further towards the question rather than the data.

Nobody standing at a van wants a stock report. They want to know whether to order the part now or risk it. That is a much narrower question than "show me the stock information", and answering narrow questions well is nearly always more useful than answering broad ones vaguely.

Which is more or less the same argument I keep making about field service software in general, so at least I am consistent.