From Raw Data to Business Decisions: Cleaning and Modeling JCars Logistics Sales Data in Power BI

Joseph K Wamahiu — Wed, 30 Sep 2026 23:29:09 +0000

The Brief

JCars Logistics imports and sells vehicles across Kenya. I was handed a raw, deliberately messy 276-row sales dataset and asked to turn it into something a manager could actually use in Power BI: a clean data model, useful DAX measures, and a dashboard that tells a real story. This article walks through what I found in the data, how I cleaned it, and what the dashboard looks like so far.

Understanding the Dataset

Each row is one vehicle sales order line: one customer, one order, one vehicle, one transaction date. There are 32 columns covering customer details, vehicle specs, branch and region, sales rep, payment, delivery, and logistics.

Before touching anything, I loaded the raw file into Power Query as a staging query called Sales_Raw, then duplicated it into a working query called Sales. Every transformation happened on the copy, so the original stayed untouched in case I needed to check back against it.

What Was Actually Wrong With the Data

A quick scan of the unique values in each column showed this dataset had more problems than expected.

Order ID had four different prefix formats (LC1000, LCL-1001, ORD1004, CAR1026), 19 rows with no ID at all, and three IDs that were reused across completely different transactions, different customers, different dates. That last one meant Order ID couldn't be trusted as a unique key on its own.

Order Date and Delivery Date each had at least four formats mixed into the same column. Things like "Aug 29, 2025" sitting next to 19/01/2026 and 26-Mar-25, plus raw Excel serial numbers like 45671, and one date that was flat out impossible: 2026-13-04, a month 13.

The categorical columns (Region, County, Customer Type, Payment Method, Payment Status, Delivery Status, Fuel Type, Transmission, Lead Source, Vehicle Type) were full of case differences, abbreviations, and genuine spelling mistakes. Not just Western vs WESTERN, but things like NRB for Nairobi, PMS for Petrol, MT KENYA used as a region, and typos like Nairobii and westen.

Car Make and Car Model had typos (Toyta instead of Toyota) and some genuinely tricky cases, like Mazda's Axela and Toyota's Axio looking similar enough to almost get merged by mistake, even though they're completely different vehicles.

Sales Rep and Customer Name had a strange corruption pattern where digits replaced letters: Dan1El Kimani, Mary Wanj1ku. Clearly meant to read "Daniel" and "Wanjiku."

Numeric fields had text hiding in them. Customer Age included the word "thirty", Units Sold had "two" and "3 cars", Vehicle Year had "Twenty Twenty" and an impossible year like 2032.

The money fields were the biggest challenge. Unit Selling Price, Unit Cost, Delivery Fee, Logistics Cost, and Revenue Recorded were all recorded in a mix of formats: KSh, KES, explicit USD, an unlabeled ? symbol that turned out to also mean USD (based on matching the size of the numbers against nearby USD-tagged rows), abbreviated millions like 4.2M, and literal error text like #VALUE!, missing, and TBD.

That's well past the ten issues the brief asked for, and a couple more turned up once the cleaning itself got underway.

Fixing the Dates Without Breaking on Blanks

Parsing the dates meant handling Excel serial numbers, slash formatted dates where I had to guess day versus month by checking which number was bigger than 12, and text month formats, all without the formula falling over on the blank rows. My first version of the logic errored on every single row, because a null value was being passed straight into a text function before I'd checked for it. The fix was simple once I found it: force every value to text, or an empty string if it's null, as the very first step before any comparison happens.

Once I had Order Date Clean and Delivery Date Clean working, I added one more check comparing them directly:

if [Order Date Clean] = null or [Delivery Date Clean] = null then null
else [Delivery Date Clean] < [Order Date Clean]

Sixteen of 276 rows, about 5.8 percent, showed a delivery date earlier than the order date. That's not physically possible. Rather than deleting those rows, since the underlying sale itself might still be real, I flagged them so they can be excluded from delivery performance analysis specifically, while still counting toward revenue totals.

Standardizing the Category Columns

For each categorical column, I built a mapping formula with a deliberate catch all: anything that didn't match a known value got labeled "Other, [value]" instead of silently disappearing into the wrong bucket. This turned out to matter a lot. Every single time I grouped by the cleaned column to check my work, something new showed up that a quick sample of 15 unique values had missed. Coast region was hiding under MSA REGION. Referral turned out to be an entire lead source that hadn't shown up anywhere in my first pass. Mazda wasn't even in the original list of car makes I'd sampled.

A few real judgment calls came up along the way, which I documented rather than just quietly deciding on my own:

Company and Corporate were merged into one Corporate customer type
held in Delivery Status was treated the same as At Yard
complete and completed in Payment Status were treated the same as Paid
Saloon was merged into Sedan, since that's just the regional British English term for the same body style
Getting the Currency Onto One Basis

All the monetary fields needed to land on a single KES basis before any of the numbers could be compared or added together. Here's the logic I used:

Values with no currency listed were assumed to be KES, per the assessment instructions. Values tagged USD, along with the unlabeled ? prefixed values, were converted at a rate of 1 USD to 130 KES. Abbreviated millions like 4.2M were multiplied out properly. Error strings like #VALUE!, missing, and TBD were set to null rather than guessed at, since there was no reliable way to reconstruct what they should have been.

The Dashboard So Far

With the cleaned columns in place, I built three core measures, Total Revenue, Total Units Sold, and Gross Profit, and put together a first pass at an Executive Dashboard.

The numbers tell a decent story on their own. Total revenue comes out to 1.31 billion KES across 452 units sold, with gross profit at roughly 430.37 million KES.

Breaking revenue down by vehicle type, SUVs dominate at 59 percent of total unit selling price, about 669 million KES, which isn't surprising for a market like Kenya but is still worth calling out explicitly rather than leaving it buried in a pie chart. Toyota leads by a wide margin on the car make table too, with 137 units sold and about 547 million KES in revenue, more than four times the next closest make.

Looking at logistics cost against gross profit side by side was a useful gut check. Logistics cost sits at about 26.99 million KES, only around 5.9 percent of the gross profit figure. That's a healthy ratio on the surface, though it's worth digging into whether that holds consistently across regions or whether some branches are quietly eating a much bigger share in logistics costs than others.

What's Still Left

This covers the cleaning work and a first pass at the dashboard, not the full detailed report pages, the complete interactivity the brief asks for (drill-through, tooltips), the full set of DAX measures, or the analyst defined questions. Those are the next things I'd build out with more time: proper time based trend measures, a real fact and dimension model instead of one flat cleaned table, and a closer look at the unusual transactions this dataset clearly still has more to say about.

The Actual Lesson Here

The thing that stuck with me most from this project: never trust a small sample of "unique values" to tell you the full picture of a column. Almost every single field had at least one hidden variant that only showed up once I actually grouped and counted the cleaned result. That habit of checking rather than assuming is really what the whole exercise was testing.

Full project and README: https://github.com/Wamahiu-hub/jcars-logistics-power-bi/blob/main/README.md

DEV Community: Joseph K Wamahiu

From Raw Data to Business Decisions: Cleaning and Modeling JCars Logistics Sales Data in Power BI