Wendy Ochieng

Posted on Oct 16

Turning Messy Data to Management Decisions: A Power BI Project for a Kenyan Vehicle Importer

#powerbi #dataanalysis #datascience

Given a flat CSV from a fictional Kenyan vehicle importer, JCars Logistics, and one instruction: turn it into something management can make decisions with.

The file had 276 rows and 32 columns. It also had six ID formats, five currencies, dates stored as text and as Excel serial numbers, and 58 different ways of writing a lead source.

This article walks through how I got from that raw file to a Power BI solution, and the reasoning behind the main decisions. The full project, code and documentation are on GitHub: https://github.com/ochiengwendy21-gif/jcars-logistics-power-bi.

What I was asked to build

JCars imports, sells and delivers vehicles across Kenya. Management wanted to understand sales, profit, vehicles, customers, branches, sales reps, payments, deliveries, returns and customer ratings, and to know what deserved attention. The brief asked for:

a data quality audit and cleaning in Power Query
currency standardisation to Kenya Shillings (KES)
a proper data model and DAX measures
a one-page executive dashboard followed by detailed report pages
drill-through, a report tooltip, slicers and navigation
insights and recommendations backed by evidence

Step 1: Understanding the data

Here, one row is one sales order line: one vehicle sold to one customer, through one branch and sales rep. Units Sold can be more than 1, so units and orders are different things. The file has 466 units across 276 orders, and getting that wrong would affect every KPI.

I split the columns into groups: identifiers, dates, customer, geography and organisation, vehicle, numeric measures and status attributes. That grouping is what later became the fact and dimension tables.

Step 2: The data quality audit

17 issues were documented. These include;

The import itself was broken by default

268 of the 276 data rows contain quoted values with commas inside them, such as "Aug 29, 2025" or "KSh 8,136,000". If you import without respecting quotes, Power Query splits those fields and shifts every column to the right. The fix is one setting, QuoteStyle.Csv, but you only know to use it if you look.

Source = Csv.Document(File.Contents("Jcars_data.csv"),
    [Delimiter=",", Columns=32, Encoding=65001, QuoteStyle=QuoteStyle.Csv])

Order ID looked like a key and wasn't

The IDs came in six styles: ord1015, ORD1004, LC1000, LCL1007, LCL-1001 and CAR1016. Nineteen were missing. My first instinct was to strip the prefixes and make them uniform, but when I checked, three numbers (1020, 1086 and 1174) were each shared by two different orders: different customers, dates and vehicles.

De-duplicating on that column would have silently deleted three real sales. Instead I generated a surrogate key (Order Key, 1 to 276) and kept the cleaned ID as a display reference only.

Money in five currencies

The price, cost, fee and revenue columns mixed plain numbers, KES, KSh, $, USD, R (rand), a corrupted ? symbol, and M suffixes like 2.04M. Adding those up would produce a number that means nothing.

Other issues

Placeholders like -, NULL, N/A and missing were used interchangeably.
Dates arrived as text in different formats, as Excel serial numbers and as impossible values like 2026-13-04.
Discounts appeared as 0.07, 20, 15% and ten percent.
Ratings were 4.5, 4.5 out of 5 or Excellent.
Make and model names were misspelled (Totoya, Mitsubish, HONDA MOTORS).
The Lead Source column had 58 distinct raw values, which are really about 10 channels.

The rule I set myself, taken from the brief: don't remove a record because it looks unusual. For each issue I decided whether to correct, standardise, flag, retain or exclude from one calculation, and wrote down why. No records were deleted.

Step 3: Currency standardisation

Everything had to be in KES, and values with no currency marker are KES by the brief's rule.

I wrote a function that reads each monetary string, detects the currency, strips symbols, applies the M multiplier and looks up a rate from a small table:

Currency	KES per unit
USD	129.40
EUR	136.90
ZAR	7.33
GBP	181.00

(Source: CBK, checked on 22/09/2026.)
The ? symbol is a corrupted pound sign, so I treated it as GBP and documented that as an assumption. One consistent rate set matters more than the exact rate. A quick sanity check: $ 37,184.62 becomes 37,184.62 × 129.40 = KES 4,811,690.

fnMoney = (v as any) as record =>
    let
        t0 = Text.Upper(Text.Trim(Text.From(v) ?? "")),
        curr = if Text.Contains(t0, "USD") or Text.Contains(t0, "$") then "USD"
               else if Text.Contains(t0, "ZAR") or Text.StartsWith(t0, "R ") then "ZAR"
               else if Text.Contains(t0, "GBP") or Text.Contains(t0, "?") then "GBP"
               else "KES"
        /* ... strip symbols, apply multiplier, look up rate ... */
    in
        [Amt = n, Curr = curr, AmtKES = n * rate]

Step 4: Cleaning in Power Query

I built the cleaning as reusable functions rather than clicking through steps: one that normalises text and looks it up in a mapping table, plus dedicated parsers for dates, discounts, ratings, units and money. The sequence was:

Import everything as text so nothing is mis-typed automatically.
Parse dates, categories and numbers with the functions.
Add the surrogate key.
Convert currencies to KES.
Flag every problem in a Data Quality Flag column.
Calculate a validation column comparing computed and recorded revenue.
Then apply documented defaults to remaining blanks.

The order of 5 and 7 matters. If you fill a missing value first, you lose the evidence that it was ever missing. Flag first, fill second.

The moment the dashboard told me something was wrong

With the first version of the report built, two things looked off:

Volkswagen showed a 79.5% gross margin while every other make sat between 2% and 24%.
A scatter plot of discount against revenue (one dot per order) had two dots floating at KES 100M to 140M, when the average order was about KES 6M.

I traced both back to the raw file. One Volkswagen order (LCL-1094) had a unit price of 47,050,000 for three cars. A Toyota Land Cruiser order (LC1192) had a unit price of 122,420,000. Both prices were about ten times higher than any comparable vehicle, and both orders also had negative recorded revenue. That pairing is what convinced me this was an extra-zero entry error, not just expensive cars.

I did not want to hand-edit two cells. I wrote a rule that applies to every row: if the price is above KES 20M and recorded revenue is negative, divide by 10. The next-highest price in the whole dataset is around KES 16.5M, so the rule catches these two orders and nothing else. The original price is kept in a separate column and the rows are flagged.

PriceFix = Table.AddColumn(PriceFlag, "Unit Selling Price KES Fixed", each
    if [Price Corrected] = "Yes" then [Unit Selling Price KES] / 10
    else [Unit Selling Price KES], type number)

The effect was large:

	Before	After
Total revenue	KES 1.64bn	KES 1.42bn
Gross profit margin	24.3%	12.3%

Two typing errors had made the business look about twice as profitable as it is. It is a good reminder that a chart can be the best data quality tool you have.

Step 5: Data validation

Row count: 276 orders in, 276 orders out.
Units: 466, unchanged.
Key: the surrogate key is unique.
Consistency: total revenue is KES 1,419,369,621 on every page and every breakdown.
Currency: conversions recalculated by hand on sample rows.
Recorded vs computed revenue: where they disagree (for example LCL1001, computed 18.27M vs recorded 9.14M), I kept both, calculated the gap and flagged the order rather than choosing a winner.

Step 6: The data model

The raw file is one flat table. I split it into a star schema:

Fact_Sales: one row per order line, with the measures and the keys.
Dim_Customer, Dim_Vehicle, Dim_Branch: one row per cleaned entity, each with a generated key.
Dim_Date: a calendar table marked as the date table.

All relationships are one-to-many with single-direction filtering. The fact table has two dates, so Dim_Date has an active relationship to Order Date and an inactive one to Delivery Date. When I want deliveries by date, a measure switches on the inactive relationship explicitly with USERELATIONSHIP. That keeps one date slicer working for the whole report.

Step 7: DAX

I put every measure in a dedicated table and only wrote ones I could connect to a business question. The core set:

Total Revenue = SUM(Fact_Sales[Revenue KES])
Gross Profit = [Total Revenue] - [Total COGS]
Gross Profit Margin % = DIVIDE([Gross Profit], [Total Revenue])

Return Rate =
DIVIDE(CALCULATE(COUNTROWS(Fact_Sales), Fact_Sales[Returned] = "Yes"),
       COUNTROWS(Fact_Sales))

Revenue YoY % =
VAR Cur = [Total Revenue]
VAR Prev = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dim_Date[Date]))
RETURN DIVIDE(Cur - Prev, Prev)

Sales Rep Rank by Revenue =
RANKX(ALL(Dim_Branch[Sales Rep]), CALCULATE([Total Revenue]))

Two pieces of business logic mattered more than any function:

Revenue = units × price × (1 − discount) + delivery fee. It is booked revenue and includes cancelled and returned orders, because the data doesn't say whether returns reverse revenue.
Gross profit deducts product cost only. Logistics cost is reported separately.

I also created a calculated column that labels each customer Repeat or One-time. A column was necessary because a measure can't be used as a chart axis.

Customer Segment =
IF(Dim_Customer[Customer Name] = "Unknown", "Unknown",
   IF(Dim_Customer[Order Count] > 1, "Repeat", "One-time"))

Step 8: Designing the dashboard

Page 1 has one job: answer "how is JCars performing?" in one glance. Six KPI cards across the top (revenue, gross profit, margin, units, average order value, repeat customer rate), a revenue trend, revenue by branch, revenue by make, and a small table of orders that need attention. Slicers for region, date and branch. I left out payments, logistics and ratings on purpose. Choosing what not to show is part of the design.

Small things that made a difference:

Nineteen orders had no usable date and showed up as a blank category, so I filtered blanks out of that visual only. They still count in every total.

Step 9: The detail pages

Page	What it answers
Sales and Vehicle Performance	Which makes and models sell and earn; how discounts relate to revenue
Branch, Region and Sales Rep	Who and where performs; which lead sources deliver
Payments, Delivery and Logistics	Payment profile, delivery speed, delays, cancellations, cost to serve
Customers, Returns and Investigation	Top customers, repeat vs one-time, returns by make, ratings, exceptions
Exceptions Table	The flagged orders and why

Interactivity with a purpose

Drill-through: from any branch or customer, a hidden Order Detail page shows the underlying orders.
Report tooltip: hovering over a make shows its model breakdown, margin and return rate.
Navigation: a page navigator on every page.

What the data says

These are the findings I would put in front of management, with what the data can and cannot support.

Two data-entry errors had hidden the real profitability. They inflated revenue by about KES 225M and doubled the apparent margin (24.3% → 12.3%).
Kakamega and Eldoret sell a lot but earn little. Together they are 32% of units and 24% of revenue but only about 10% of gross profit, with margins of 5.8% and 4.1% against a 12.3% average. Nairobi HQ earns 25.2% on 5% of revenue. The data cannot tell me why; discounting, vehicle mix and cost data need checking.
The slowest-delivering branches also cancel the most. Nairobi HQ averages 26.3 days (company: 15.2) and cancels 33% of orders. Kisumu averages 19.1 days and cancels 25.8%. The samples are small, so it points to a process review, not a conclusion.
Nearly a third of orders are returned. 88 of 276 (31.9%), plus 42 cancelled (15.2%), all of which sit inside the headline revenue. Honda (38%) and Mitsubishi (37%) are highest among makes with meaningful volume.
About 40% of booked value is not fully paid, and cash and M-Pesa carry roughly half of all value on vehicles averaging KES 5.1M per order, which is worth validating with finance.
Repeat customers are worth about 1.6× more per customer (≈KES 9.9M vs 6.0M), yet 64% of named customers buy only once and half of revenue comes from them.
Ratings show no visible link to revenue. Average ratings sit between 3.0 and 4.0 whatever the size of the make.

What I would recommend

Add validation at order entry (price ranges, no negative revenue, mandatory cost) and reconcile recorded against computed revenue monthly.
Review pricing, discounting and costs at Kakamega and Eldoret before pushing more volume there.
Review order-to-delivery at Nairobi HQ and Kisumu and start recording cancellation reasons.
Record return reasons and report net revenue beside gross.
Chase pending and part-paid orders and check how large cash and M-Pesa payments are recorded.
Pilot a follow-up programme for one-time buyers and track the repeat rate.

None of these claims causation. They say where to look first.

What I would do differently

Check key columns for uniqueness before building anything on them.
Decide earlier whether revenue is gross or net; I only fully appreciated the question after seeing how many orders were cancelled or returned.
Use rates for each order date rather than one fixed set, if the data had justified it.
Log every default and assumption in a table as I go, not afterwards.

What I learned

A model is only as good as its grain and keys.
Flag first, fill second, so the evidence survives.
A documented rule beats a hand edit, and always keep the original value.
Outliers are findings as well as problems.
A dashboard should be designed for the person reading it with no analyst in the room: summary first, detail behind it, and drill-through for the records.

DEV Community