What public datasets don’t tell you
Six datasets, six Power BI builds, and a pattern. None of them was dirty in the way people mean when they say dirty data. Every one of them contained something that changes the answer and announces itself nowhere.
There is a particular kind of bad data everyone is ready for. Nulls where there should
be numbers, dates as text, a column of #N/A. You spot it in the first
five minutes because it stops you.
The dangerous kind does not stop you. It loads cleanly, it aggregates, it produces a chart that looks entirely reasonable, and it is wrong. Over six builds — retail orders, a general ledger, a decade of card transactions, five years of HR records, three years of a retailer’s shipments and the supervisory returns of 129 EU banks — the same handful of shapes kept turning up. Here they are.
1. The key that is not a key
The Superstore extract has a Product ID column. It is the obvious primary
key for a product dimension, and it is not one. 32 IDs carry two different
product names, and 16 names sit under two different IDs.
Key on the ID and you merge products that are genuinely different. Key on the name and you split ones that are the same. Either way nothing errors: you get a product dimension, it joins, the report renders, and some of the numbers are quietly attached to the wrong thing. The fix was to make the dimension the (ID, name) pair — 1,893 of them — behind a surrogate key.
A column being named ID is a claim, not a guarantee. It costs one
groupby to check, and almost nobody does.
2. A label is not an event
The HR extract has an employment status for every person, and a start and exit date. They disagree 1,146 times. 991 people are “Active” with an exit date that has already passed. All 69 “Future Start” employees started years ago and have since left. Everyone on leave of absence has an exit date too.
Count headcount off the status column — the obvious thing, because that is what it is for — and August 2023 reads 2,613 against 1,480 from the dates: 77% too high. Nothing errors. The status field simply records what somebody last remembered to type, and the exit date records what happened.
A status is a label someone has to remember to update. A date is an event.
3. One column, two meanings
The card transactions file has a merchant_state column. It holds 199
distinct values. Fifty-odd are two-letter US state codes. The other 147 are
full country names.
Put that column on a map visual and it mostly works, which is the problem. It works well enough that you would not look twice — and it plots “Georgia” the country onto Georgia the US state, because the column contains both.
The blanks in the same column are a second version of the same trap. Almost all of them are online transactions, where there is no merchant location to record. But not all: 845 card-present travel-agency rows are blank too. Relabelling every blank as “online” — the obvious tidy-up — would have moved card-present spend into the online channel, which is exactly the number a fraud team would be looking at.
4. The file you were given is not the file you think
Two files arrive together and you assume they describe the same population. In the card dataset, the customer file has 2,000 clients. 781 of them never appear in the transaction file at all, and 2,075 of the 6,146 cards never transact.
Compute spend per customer over the customer table and you divide by 2,000 when you should divide by 1,219: the figure comes out 39% too low, and there is nothing on screen to suggest it.
The HR extract goes one further. Its recruitment file numbers applicants 1001 to 4000 — exactly the range of the employee IDs — so it joins to the employee table perfectly, to the wrong people. On a shared ID, not one of 3,000 names matches. None of the applicants applied for a job title that exists in the company. A shared key range is a coincidence, not a relationship, and the only safe thing to do with that file was test it and leave it out.
5. The measure column that is not what it says
The card dataset has an Amount column. It is the transaction value, and it
is filled in on declined transactions too — because it is the amount that
was attempted, not the amount that settled. SUM(Amount) overstates spend
by $1.64m over ten years. That is not an error in the data; it is the
money the bank declined to move, and it belongs in a different measure.
The supply chain extract has a column called Sales. It is price times
quantity before discount, on every order including the ones that were cancelled. The
money that changed hands is in a column called Order Item Total. Sum
Sales and revenue reads 16% high. Two columns further
along, “Order Profit Per Order” turns out to be per line, and “Benefit
per order” is an exact copy of it under a different name.
6. The labels that quietly stop
The card file ships fraud labels. They cover 67% of transactions. Not 67% of one year — 67.0% of every year, which tells you it is a sampling decision by whoever built the file rather than a period nobody got round to reviewing.
Join the labels on and the unlabelled rows come through as “not fraud”, because that is what a left join does. The fraud rate then reads 11.0 per 10,000 instead of 16.5 — a third low, from exactly the same confirmed cases. The only fix is to treat “not labelled” as its own state and never let it into the denominator.
7. The period that is not a period
Three of the six had an edge you could fall off.
- The card data stops on 31 October. Compare 2019 against 2018 without noticing and you report a 17.6% collapse in spending that is entirely the calendar.
- The supply chain file changes generator on 3 October 2017. From that day every order has one line instead of one to five, orders arrive at 68 or 69 a day with almost no variation, and 100 of the 101 products on sale are replaced by 17 new ones. A trend line drawn across the join reports a collapse in basket size; what actually happened is that a second dataset was appended to the first.
- The bank returns report profit year to date: March is three months, December twelve. Add up a year’s four reported figures — the obvious way to get a year — and 2024 profit reads €489bn instead of €196bn, because January has been counted four times.
A partial or overlapping period is the single most common way a report tells a board that something collapsed, or boomed, when nothing did.
8. The sample that is not a panel
In the Superstore data, new customers fall from 589 to 141 to 52 to 11 across four years, and retention rises the whole way — 72% to 81% to 87%.
Read naively that is a business with a collapsing top of funnel and world-class loyalty. In fact it is neither: it is what happens when a sample is drawn by order rather than by customer. Customers can only enter the panel by having an early order, so acquisition can only fall and retention can only climb. There is no acquisition story in that data, and the honest response is to build the customer page around retention and say why.
The bank returns have the opposite problem. Banks join and leave between exercises, so a sector ratio drawn over whoever reported that quarter moves every time the membership does. The trend there is drawn over the 101 banks present in all twelve quarters, and nothing else.
9. The average that describes no one
The bank returns give every bank’s CET1 capital ratio. Average that column across the 119 banks reporting in June 2025 and you get 22.7%. Add up the capital, add up the risk-weighted assets and divide once, and the sector ratio is 16.2%. The median bank sits at 18.0%.
All three are correct answers to different questions, and the first one describes no bank and no banking system. The average gives a lender with almost no risk-weighted assets the same vote as one with a trillion euros of them — and three such lenders report ratios of 74%, 89% and 353%. Leave them out and the average is still 2.6 points above the sector, because the small banks carry the most capital per euro of risk.
And one that is just badly filed
Worth including because it is so ordinary. The European Banking Authority renumbers
every line item in every annual exercise: CET1 capital is 2320102, then
2420102, then 2520102. Stack three years of files and you get
three unrelated series that happen to share a label. The map from old codes to new sits
in a separate data dictionary, and the build fails if a single row will not map. No
error otherwise, no null — just three short series where there should be one long
one.
How you actually find these
Not by staring at the data. Almost all of the above were found by one of three things:
- Counting the same thing two ways and comparing. Every measure in these builds is computed twice — once in DAX, once in pandas with no DAX involved — and diffed. That is what turned up the 781 missing clients, and a “customers active in every year” measure that returned 793 when the true answer was 293.
- Asserting the thing you believe. If a subtotal must equal the union of its lines, write that as an assertion in the ETL and let it fail. On the P&L build that caught a missing interest-expense account in Net Income. On the bank build it is why ten risk-weighted-asset lines are proved to add to the reported total, for every bank, in every quarter.
- Asking whether a value is plausible, not whether it is valid. An employee marked Active with an exit date two years ago is a perfectly valid row. It is not a plausible one. No type check will ever tell you the difference.
Clean data is not data without nulls. It is data whose every column means what its name says it means — and that is a claim you have to test, not a property you can inherit.
None of this is unique to public datasets. Public data just has the advantage that I can show you the workings. The same shapes are in every extract a client has ever sent me, with the difference that nobody has published a caveats section for those.
Every finding above is written up in full on the build it came from, with the code that catches it: Superstore sales, P&L performance & outlook, card transactions & fraud, workforce analytics, supply chain delivery and EU bank capital.