Skip to content

Technology | People | Progress A Brighter Tomorrow

Get in Touch
Abstract graphic of a grid of data cards in the Logic Networks colours, introducing the article on cleaning data before reporting

November 19, 2026

Cleaning up data before you report on it

Every reporting project discovers the same thing in its first week. The data is messier than anybody believed, and the mess has been quietly changing the numbers people already act on.

These five checks find most of it, and they can be run before anybody builds a single chart.

Duplicates, including the ones that do not look like duplicates

The same customer entered twice with a slightly different name. The same site recorded once with a postcode and once without. The same pupil in two year groups because of a transfer.

Exact duplicates are easy and rare. The expensive ones differ by a space, a full stop or an abbreviation, so they survive every simple check and count as two.

Sort by the field you care about and read a hundred rows by eye. Ten minutes of that finds patterns no automatic check will suggest, and it tells you what to look for afterwards.

Empty fields, counted before you group by them

If you are about to split results by region, count how many rows have no region first.

A field that is missing on a small number of rows is a footnote. One missing on a fifth of them makes every split by that field misleading, and nobody looking at the finished chart would ever know.

Count the blanks for every field you intend to group or filter on. Write the counts down, because they belong in the note under the chart.

Five cards: Duplicates, Empty fields, Dates, Free text categories, Joins

Dates, which are always worse than expected

Dates cause more silent errors than anything else. Two formats mixed in one column, so the same day reads as two different months depending on where the row came from. Placeholder dates standing in for unknown. Impossible dates that a system accepted years ago.

Check the earliest and latest value in every date field. Anything before the organisation existed, or in the future, is a placeholder and needs a decision rather than an average.

Then check whether the column is genuinely a date or text that looks like one. Text sorts alphabetically, which puts everything from a given day of the month together and produces charts that are convincing and wrong.

Categories that people typed

Any field people type freely ends up with a dozen versions of the same answer. Different capitalisation, plurals, abbreviations, a trailing space.

A count of the distinct values tells you immediately. If a field that should have six categories has forty, most of them are the same six.

Fix it in two places. Tidy the existing values, and then change the form so that the next twelve months do not produce the same problem. Only the second one is a real fix.

Joins that quietly lose rows

When two tables are combined, rows that do not match anything in the other table disappear. That is usually the intention and occasionally a disaster.

Count the rows before and after. If the number fell, find out which rows went and why. Often it is a small group with a slightly different identifier, and often that group is the interesting one.

This single check catches more wrong totals than anything else on the list.

Write down what you found

Keep a short note of every problem, what you did about it, and what you chose to leave. It becomes the footnote under the report and it stops the same discussion happening every quarter.

It also builds the case for fixing things at source. A list saying which fields are unreliable, with counts, is far more persuasive than a general complaint about data quality.

Fix upstream where you can

Cleaning the same problem every month is not a solution, it is a subscription. Where the cause is a free text field, a missing validation rule or a process that lets people skip a step, fix that instead.

Cleaning scripts are for history. Form changes are for the future, and the second one is the cheaper of the two.

We do this work before building any reporting. There is more on our data and AI page, and the automation side is on automation. If you are not confident in a number that appears in your board pack, tell us which one and we will say how we would check it.