Introduction
JCars Logistics sells vehicles out of nine yards around Kenya — Nairobi, Mombasa, Kisumu,
Eldoret, Kakamega and a few others. The business wanted answers to three plain
questions: where do our sales actually come from, what do they earn us, and can we trust vehicles to reach customers on time?
The data I was handed couldn’t answer any of that.It was 273 order rows across 55 columns, and when I checked, only 69 of those rows barely a quarter were clean enough to use.The rest had something wrong with them.
Investigating the data before touching it
I didn’t open the file and start cleaning. First I wanted to know whether I could trust any of it.
In Power Query I switched on Column quality, Column distribution and Column profile so I could see blanks and distinct values in every column at a glance. Then I started poking at the business logic. Does the recorded revenue match what the price, units and discount say
it should be? Do delivery dates come after order dates, like they obviously should? Does each branch stay inside one region?
Those checks surfaced five families of problems.
Identity. 19 rows had no Order ID at all, and the ones that did used five different prefixes ORD, CAR, LCL, LCL-, LC. Without a key you can rely on, you can’t spot duplicates and you can’t build safe relationships.Prices, costs and fees were sitting in four currencies: KES, USD, EUR and ZAR.
When I compared the converted figures against the originals, the implied rates were suspiciously constant (USD 129, EUR 151, ZAR 7.91), which told me someone had applied a fixed booking rate. Some rows had a currency I couldn’t identify, 10 showed negative revenue and 102 flat-out failed my reconciliation check. That check came straight from the
data: Net Sales = Selling Price × Units × (1 − Discount), and the recorded revenue should
equal either that or that plus the delivery fee. 102 rows matched neither.
Time. Dates were stored as long text, like “Wednesday, March 26, 2025”. Once I converted
them, the problems jumped out: 14 orders were apparently delivered before they were
ordered (one of them 290 days before), 23 dates wouldn’t parse at all, and 56 were simply missing.
Categories. The same thing was written several ways. Person and Individual. N.G.O and NGO, Saloon and Sedan, Wire, RTGS and Bank Transfer. Paid and Completed. Every spelling variant quietly splits a total that should have been one number.
Logic. Delivered orders marked “Returned”. Cancelled orders carrying a “Completed”
payment. Discounts as high as 50%. Vehicle years of 2026.
Cleaning and preparation:
what I did, and why For every fix I wrote down three things — what I did, why I did it and what I was assuming when I did it. The assumptions matter more than the clicks.
Steps What I did Why, and what I assumed.
Keys Standardized the Order IDs and added a surrogate key.I assumed the number identifies the order, so LC1000 and LCL1000 are the same order, just typed differently.Types Converted dates, money and flags to proper data types.Text dates break every time-intelligence function Currency Converted everything to KES at the rates the data implied.I assumed those were the business’s own booking rates.Revenue Rebuilt Revenue as
net sales plus delivery fee.The recorded figure failed reconciliation on 102 rows. ORD1009, for instance, had no recorded revenue, but the rule gives 2,920,000 × 0.93 +140,000 = 2,855,600
Dates Kept the impossible date rows but flagged them I assumed the order is real and the date is just wrong — deleting it would throw away a genuine sale Categories Merged the variants with a mapping table Cleaner slicers, and totals that actually add up Missing value State each choice and what it changed Unit economics Added Unit Selling
Price and Unit Cost columns needed them to work out cost and margin per
vehicle
A few of those decisions came back to bite me, and I caught them during validation:
Zeros aren’t blanks. A missing logistics cost that loads as 0 makes an order look more profitable than it really was. That’s not a rounding issue — it’s a wrong answer.
A currency bug hiding in plain sight. One delivery fee read 426.67 sitting right next to a
logistics cost of 74,000. That 426.67 is clearly a USD figure that never got converted
(426.67 × 129 ≈ 55,000 KES)
A duplicated customer ID. After one of the merges I ended up with both Customer ID and dim_customer.Customer ID in the fact table. I kept one and dropped the other.
Modelling the data
I built it as a star schema: one fact table (facts table, 276 rows) with five dimensions around
it — dim_customer, dim_date, dim_location, dim_sale rep and dim_vehicle.
Why a star schema rather than one big flat table?
• Attributes like region, make and rep fall in exactly one place, so when I fix something, I fix it once.
• The relationships are plain one-to-many, which keeps the DAX predictable and the reports quick.
• dim_date is marked as the official date table, so time intelligence works properly.
Delivery Date is a second date column, so it hangs off an inactive relationship and only wakes up inside measures that call USERELATIONSHIP.
One limitation I made a point of writing down: the customer ID seems to track the source
row number, which means a repeat buyer can get counted as several different customers.
There are 181 distinct names across the 273 rows. So read any customer count with that
caveat in mind — it’s an upper bound, not gospel.
The DAX behind the numbers
Each measure exists to answer one specific question. [Swap in your own measures.]
Total Revenue =1.35bn
CALCULATE
Total Sales =SUM('facts table'[sales])
Total= 1.82bn
SUMX(
FILTER('facts table', 'facts table'[Delivery Status] <>
"Cancelled"),
'facts table'[Units Sold] * 'facts table'[Unit Cost] + 'facts
table'[Logistics Cost]
)
Unit Cost is per vehicle, so just summing the column would be nonsense. SUMX multiplies
by units first, row by row.
Gross Profit = [Total Revenue] - [Total Cost]
total gross profit = 454.68m
Gross Margin % = DIVIDE([Gross Profit], [Total Revenue])
Gross Margin = 25%
Designing the report
I built the report around the three questions, one page each.
Executive overview. KPI cards for Total sales, Gross Margin %,total cost and Total revenue recorded.
Sales analysis. Revenue sliced by branch, sales rep, lead source, make and vehicle type,
with slicers for year, region and make. This is the “where does the money come from?”
Operations and delivery. Delivery days by branch, the spread of delivery times, and a table of the orders that ran late.
A few design calls I stuck to:
• A small palette, used consistently — the same colour always means the same thing.
• Bars sorted by value, never alphabetically.
• Drill-through from region down to branch down to the individual order.
• Chart titles that say what you’re looking at, e.g. Revenue, total cost
Insights
• Riftvalley region brought in Ksh.3,044,400 of revenue, while Western managed only Ksh.1,872,800
• online produced the most orders, but walk in had the highest average
order value.
• SUV gave the best gross margin at 14.72%, against 4.22% for Toyota.
Recommendations
Pin each recommendation to an insight above:
• Move marketing spend toward the lead sources with the best margins, not just the
most orders. Volume and profit aren’t the same thing.
• Dig into the slow branches. Delays at [branch] are probably costing repeat business.
• Revisit pricing and discounts on the lowest-margin makes.
• Fix the data at the point of capture. Making currency and Order ID mandatory fields
would have prevented most of what I found — remember that 75% of rows needed
correcting.
what i took away from it
Cleaning ate up most of the time, and that’s exactly where the real analytical decisions got
made. The assumptions I logged turned out to matter as much as any chart.
Checking my own work paid off. Validating the converted values and the filled-in blanks
caught errors that would otherwise have shipped without a sound.
I kept quality visible by holding on to an issues flag, so anyone can filter the report down to
clean records and judge for themselves.
And the big one: missing is not zero. Treat a blank cost or a blank revenue as 0 and you’ve silently poisoned every average built on top of it.
If I did this again, I’d fix the capture process first. Clean inputs mean far less cleaning later.
Conclusion
This project took the JCars Logistics dataset from a raw, inconsistent Excel file to an interactive Power BI report that management can use to make decisions. The process started with investigating and cleaning the data: fixing data types, handling missing values and duplicates, and standardizing text fields. I then shaped it into a star schema, with one facts table linked to the customer, date, location, sales rep and vehicle dimensions. That model made the DAX measures (revenue, gross profit, margin, logistics cost ratio, return rate and time comparisons) accurate and easy to reuse across every visual.
Top comments (0)