Asset data the finance team can count on

Two findings, both from reading the vendor's API documentation end to end and then testing it against production rather than trusting either one alone.

Finding one The extract had been reading one page of nine

The existing production script fetched page one and reported success. It had been doing that quietly for long enough that nobody questioned the totals.

The cause is easy to miss and impossible to see in the output: this API paginates with an RFC 5988 Link header, not the ?page=2 convention almost every other API uses. If your code doesn't parse that header you receive the first hundred records, a 200 OK, and no indication whatever that more exist.

Measured against the fault-reports endpoint:

801 records across nine pages. A page-one-only read sees 100 of them: 12.5%.

Nothing errors. Nothing warns. The report renders, and it is wrong by a factor of eight.

Finding two The key data cannot come from the API

The pre-start records (the safety checks, with the odometer and hour readings inside them) are write-only. You can push a pre-start into the system; there is no endpoint that returns one. I verified this across the entire vendor documentation repository, then proved it against production: GET on that endpoint returns 404.

That matters more than it sounds. It means the obvious architecture (one API, one pipeline, one table) was never available. Any register combining asset details with pre-start readings has to join two sources of different kinds: an API, and a spreadsheet export from the vendor's web reporting.

An hour of reading the documentation properly changed the design more than any amount of coding would have. The build I would have shipped on day one would have been confidently wrong.

The design that survived the investigation:

• The asset register comes from the API: every vehicle, plant item and piece of equipment, with its make, model, type, group and service status.
• The pre-start readings come from the vendor's own web report, which I export by hand. There is no endpoint to automate against, so there is nothing for a scheduler to call. One row per pre-start event, so a single asset has many rows.
• The join is the fleet number, the one key both sides genuinely share.

The hard part is not the join. It is the word latest.

The pre-start export is a transaction register: hundreds of rows, many per asset, going back months. What the report needs is one row per asset: its most recent completed check. That reduction is a business rule, and getting it wrong does not produce an error. It produces a plausible old number.

• Keep only completed checks: an abandoned check evidences nothing.
• Within each asset, sort by date descending, then by pre-start number descending.
• Take the first row.

Why the second sort key exists: the export carries a date, not a timestamp. Two checks on the same asset on the same day are indistinguishable by date alone. The pre-start number is sequential, so the higher number is the safer proxy for "later". Without that tie-breaker the rule silently picks whichever row the tool happened to return first.

The extraction. A scheduled flow runs daily at 5am, before anyone is looking at a report. It:

• walks every page by following the Link header until the header is absent, the fix for the original defect;
• asserts that the number of records collected equals the total the server declared in its own count header. A mismatch is a hard failure, not a logged warning;
• writes the raw response to a document library before anything parses it, so a flattening mistake is fixed by re-reading a file rather than re-querying the API;
• respects a rate limit of 20 requests per 15 seconds by running at 18, a documented architectural constraint, not something to discover in production;
• emails on failure, with the error handler tested rather than assumed.

The transformation. The register is built in Power Query: the asset dimension from the raw JSON, the latest-check snapshot from the export, then a left join on fleet number so every asset appears exactly once, including the ones that have never been checked, which are precisely the ones a compliance question is about.

The fix is to sort inside each group rather than trusting an earlier sort to survive it, a one-line change in shape, and the difference between a register you can hand to finance and one you cannot.

The wrong number that looks right is more dangerous than the crash. A crash tells you. A stale odometer reading does not.

Use one Pre-start compliance

One row per asset showing when it was last checked, by whom, and whether it has been checked at all. That turns "are the crews doing their checks?" from an impression into a list, including which sites are consistent, and which assets have gone untouched.

Use two Fuel Tax Credit substantiation

Fuel Tax Credits are claimed on fuel used in eligible business activity, and a claim is only as defensible as the usage evidence behind it. The register gives the finance team per-asset kilometres and operating hours taken from the operators' own recorded checks, rather than an estimate.

This is the part I care about most: a finance outcome produced by an engineering fix, and auditable back to a source record.

And What it found on the way

Looking at every asset at once surfaced data problems nobody had measured:

• The same physical asset entered twice under two different fleet numbers, in one case proven by an engine number appearing inside the other record's serial number. Any count of "how many of these do we own" was wrong.
• Two active records sharing one registration, so registration was not the unique key it appeared to be.
• Build dates stored as free text: five different formats plus blanks in a single pull. Cannot be cast to a date without a parser and a fallback.
• Sold assets returned by default, with no filter. Anything counting "the fleet" over-counts unless it excludes them explicitly.
• Empty strings and nulls used interchangeably in the same column, so the same condition behaves differently on different rows.

None of these are extraction bugs: the extract is faithfully reporting what the system holds. They are ownership problems that were invisible until something looked at every asset at once. Surfacing them is part of the value, not a side effect.