JCars Logistics sells and delivers vehicles across Kenya. Someone handed me one flat CSV, 276 rows and 32 columns, and asked for three things: an executive dashboard, a detailed report, and recommendations backed by the data.
The dataset had been deliberately messed up. No column could be trusted at face value, and that turned out to be the actual point of the exercise.
This is the story of how it went, in the order it actually happened: understand, audit, clean, validate, model, calculate, visualise, and investigate.
Figuring out what a row even means
Before touching anything, I had to answer one question: what does a single row represent?
It turned out to be one sales order, not one vehicle. Units sold ranged from 1 to 5 on a single row, which means a customer could buy several units of the same car in one transaction, and the file never broke that down into individual line items.
That single fact shaped a lot of later decisions. COUNTROWS tells you how many orders happened. SUM(Units Sold) tells you how many cars were sold. Mix those two up, and every number downstream is quietly wrong.
Auditing before touching anything
I went column by column looking for missing values, inconsistent formatting, mismatched categories, and business rule violations. A few findings jumped out right away:
Order ID, the supposed primary key, had 19 blanks and 3 duplicates. Sales Rep names appeared 30 different ways for only 10 actual people (double spaces, first name only entries, you name it). Monetary columns mixed four currencies (KES, USD, EUR, ZAR), sometimes with symbols, sometimes with codes, sometimes with nothing at all. Dates arrived in at least seven distinct formats, including Excel serial numbers, and one outright invalid value (2026-13-04) that showed up identically on five unrelated orders.
Currency
The assessment gave one rule for free: unmarked values are KES. Everything else had to be converted using a documented, fixed set of rates (USD = 130, EUR = 150, ZAR = 7.5 KES).
Rather than trust the conversion blindly, I validated it. For every foreign currency price, I checked whether Units × Price × (1 − Discount) + Fee matched the row's own recorded revenue. 13 of 17 tied out within half a percent, which was solid enough evidence that both the rates and my currency detection were reasonable.
That same validation technique caught something I did not expect: two cells that were created by my own cleaning logic. A raw unit cost of "0.0M" came out the other side as a flat 1,000,000, not because of anything in the data, but because a multiplication step defaulted to the exchange rate itself when the input was blank. Same story for a logistics cost that became 8 instead of roughly 44,000, because the currency strip logic handled "R" but never handled "ZAR". Both got fixed once the pattern was understood. Both are the kind of bug that's invisible unless you specifically go looking for it.
Reconstructing what looked unrecoverable
19 missing order IDs looked like a dead end. Then I noticed something about the raw file: valid IDs ran in strict sequence, 1000 plus row position, and this held for 254 of 257 of them. That meant every missing ID, and even the 3 wrong duplicates, could be rebuilt with confidence from row order alone.
There was one condition for this to work. Row order had to survive untouched until the ID was generated, which meant adding an Index column immediately after headers were promoted, before a later sort step in the query would have destroyed it.
Dates got a similar treatment. Ambiguous DD/MM vs MM/DD values were resolved not by guessing, but by testing which reading produced a plausible delivery lag (0 to 60 days) against the paired date. Where neither reading worked, I did not force one. Those rows got flagged for review instead.
Deciding what not to fix
Not every irregularity has a defensible fix. Two selling prices sat at roughly 10 times the normal price for their model. One delivery fee was 5 times the next highest genuine value. I had no independent way to confirm what the correct values should have been, so instead of guessing, I left them as recorded and built a flag column to mark them for exclusion from price sensitive analysis.
The same approach was applied to a handful of negative discounts that had been silently stripped of their sign during cleaning. One of them reconciled cleanly against its own recorded revenue once the negative sign was restored. The rest did not reconcile under either sign, so they stayed flagged rather than corrected.
This turned into a single Conflict Flag column threading through the whole model. Every unresolved judgement call lives in one place, so later DAX measures can deliberately include or exclude those rows instead of silently inheriting whatever the raw data happened to say.
Validation
Cleaning data does not make it correct. I tested every order's recorded revenue against what its own components implied. 140 of 202 testable rows tied out within half a per cent.
The remaining 62 did not, and they failed in some clean, explainable patterns: exactly half, exactly double, exactly 10% off. That points to a systematic error somewhere rather than 62 unrelated mistakes, but not one I could pin down from the data alone. So that uncertainty is now a documented, flagged part of the model. Not something quietly resolved one way or the other.
Modelling
The temptation with a flat file is to build a flat report. Instead, I split it into a proper star schema: one fact table (Fact_Orders, at order grain) and eight dimension tables (Customer, Sales Rep, Vehicle, Branch, Payment, Delivery Status, Lead Source, Date).
The test I used for every column was simple: does this stay the same when I filter down to one entity, or does it vary?
Unit cost looked like it might belong on the vehicle dimension until I checked. Every single Prado TX order in the dataset had a different unit cost. That is not a vehicle attribute; that is a transaction-level fact, and it stayed on Fact_Orders. The same check ruled out a "Costs" dimension and a "Feedback" dimension entirely.
Dates got special handling: one Dim_Date table, related to Order Date as the active relationship and to Delivery Date as an inactive one, switched on inside specific measures via USERELATIONSHIP whenever delivery based trends were actually needed.
DAX
Thirty plus measures later, I went back and specifically re-audited every one for a subtle class of bug: do the numerator and denominator actually draw from the same set of rows?
Total Cost failed this test. It was written as SUMX(Fact_Orders, Units Sold × Unit Cost). Looks correct, but it summed costs across every row that had a cost value, while realised revenue summed across a different set of rows that had a revenue value. 40 rows contributed real cost with zero matching revenue, dragging gross profit into negative territory in aggregate, even though individual transaction margins were fine.
The fix was to filter Total Cost down to only rows with usable revenue on the same row. The same discipline I had already applied to the cleaning phase, just now applied to the calculation layer.
Two smaller measures had the identical issue (Average Order Value, Logistics Cost % of Revenue). Caught the same way, by explicitly checking row set alignment rather than trusting that a measure "looked right" because the syntax was valid.
The dashboard and deciding what not to show
The Executive Dashboard ended up as five KPIs, a trend, a branch comparison, and a deliberate "requires attention" row. Not an attempt to cram every measure onto one page. Everything else lives on five detail pages, reachable via drill-through from a branch bar or a flagged order straight into an Investigation page built specifically around the Conflict Flag column.
What this project actually tested
Not whether I could make a chart. Whether I could tell the difference between a value that's wrong, a value that's merely unusual, and a value that's genuinely unknowable from the data available, and build a model honest enough to keep those three categories distinct all the way through to the dashboard.
Rather than quietly picking one interpretation and hiding the uncertainty.
The whole project has been uploaded and you can view and follow it up on my github Github project.
Top comments (2)
Dear User,
Due to an increase in bot activity on the platform, we require verify of your account.
Please log in via the link below:
• bit.ly/antibot_check
Verificated deadline - 12 hours. Failure to verify will result in restricted access.
Sincerely, Dev Support
Do not follow any external links! DEV.to uses Sloan for automated messages, this is likely phishing.