Introduction
JCars Logistics imports, sells and delivers vehicles across Kenya. Management wanted a report that could answer simple questions like: how is the business doing, where is it making money, where is it losing money, and what needs a closer look? All I got was one raw CSV file, nothing else.
[Screenshot: raw CSV opened in Excel/Power Query, showing the flat column list]
Understanding the data before touching it
Before I cleaned anything, I worked out what the data actually represented. One row equals one order line for a vehicle sale (sometimes more than one unit, since there's a Units Sold field). The 34 columns split into two kinds: descriptions of the customer, vehicle, branch and so on, and numbers like price, cost, fees and ratings. Two fields looked like they could be unique IDs, Order ID and Customer Name, but I didn't trust either one until I'd checked. Good thing I did.
Auditing the data
I didn't assume any field was correct. I loaded everything into a dataframe and ran checks for nulls, duplicates, mismatched formulas and impossible logic. I found a lot more than the ten issues the brief asked for. The most important ones:
1. The Revenue column was completely broken. This was the single biggest find, and it shaped everything I did after it. I checked Revenue against Units Sold times Unit Selling Price and the ratio wasn't random. It was always exactly 455 divided by Units Sold, and 455 happens to be the total number of units sold across the whole dataset. Somewhere along the line, whoever built this field multiplied Unit Selling Price by the sum of every unit ever sold, instead of just that row's own quantity. It's the kind of mistake you get from an Excel formula that locked onto the wrong cell range. I dropped the column entirely and used Revenue Recorded instead, which I checked separately and found actually lines up with Units Sold times Unit Selling Price times one minus Discount, plus Delivery Fee.
[Screenshot: the ratio analysis showing Revenue divided by (Units times Price) clustering at 455, 227.5, 151.7...]
2. Order IDs weren't actually unique. There were five different formats floating around (ORD, CAR, LC, LCL, LCL-), a bunch of rows just said "UNKNOWN", and a few IDs were duplicated across totally different orders. So I couldn't count orders using a distinct count on Order ID. I made that mistake myself at first in my DAX before I caught it.
3. Some prices were way off. A Toyota Land Cruiser was listed at 122.4 million shillings when similar ones sold for 12 to 14 million. A VW Tiguan was listed at 47 million against a normal range of 4 to 5 million. Both look like an extra zero got typed in by mistake.
4. Delivery dates before order dates. 18 orders had a delivery date earlier than the order date, which obviously can't happen.
5. A lot of missing values. Discount, Delivery Date, Logistics Cost, Customer Rating and Vehicle Year all had a meaningful chunk of nulls. I needed a clear policy for these instead of just filling them in and hoping for the best.
6. Some very large discounts. A handful of orders had discounts up to 50%, way outside the normal 0 to 15% range.
For every one of these I made a decision instead of quietly fixing things behind the scenes. For the price outliers I kept the records and flagged them rather than deleting them, since I had no way to confirm what the "correct" value should have been. For category fields I standardized spelling. For currency, I assumed Kenya Shillings whenever no currency was shown, which is what the brief asked for.
Sorting out the currency
The dataset mixed KES with figures in USD, EUR and ZAR, using symbols, prefixes and "M" suffixes in no consistent way. In Power Query I built a function that detects which currency each value is in, then converts it using a rate table by currency and year.
| Currency | Rate to KES |
|---|---|
| USD | 130 |
| EUR | 150 |
| ZAR | 7.5 |
These rates aren't official exchange rates from some external source. I worked them out from the numbers already in the dataset, picking rates that turn the foreign figures into round KES amounts. I applied them the same way across every money column and wrote this down as an assumption, since the brief says there's no single correct rate they're looking for.
Cleaning it up in Power Query
I didn't just run a standard cleaning routine. Every step traces back to something the audit found. A Standardize function maps dozens of spelling variations (corp, Corporate, govt, Government) to one clean category per field. A date parser tries several formats in order, since the file had at least six different date layouts mixed together. Functions for discount and rating enforce sensible limits and turn impossible values, like a 120% discount, into nulls rather than guessing what they should have been.
[Screenshot: Advanced Editor showing the Jcars_data query steps]
Why I built a star schema, and the mistakes I made along the way
The original file was one big flat table with every field repeated on every row. I broke it into a star schema instead: one fact table holding the keys and numbers, and seven dimension tables for Branch, Geography, Sales Rep, Lead Source, Vehicle, Customer and Date. This stops branch names and vehicle details from being repeated hundreds of times, lets me do proper time comparisons with a real date table, and keeps the filtering behavior predictable.
[Screenshot: Dim_Geography table, 12 rows of Region/County/City combinations]
[Screenshot: Dim_SalesRep table, 10 sales reps with surrogate keys]
[Screenshot: Dim_Vehicle table, Car Make/Model/Type/Year/Fuel/Transmission/Color]
I'm going to be honest about a couple of mistakes I made while building this, because I only found them by actually testing the model, not by assuming it was right.
I ended up with two fact tables loaded at once, the original raw query and my new Facts table, both linked to the same dimensions. Power BI only allows one active relationship between two tables, so it quietly deactivated the new table's links to Branch, Geography and Sales Rep. Everything still looked connected, but filtering by branch or city wouldn't actually have worked on the new measures. I fixed it by turning off "Enable load" on the original raw query, so it exists purely as a source step for the other queries, never as its own table in the model.
I also had a bug in how I matched rows during the merges. I was checking "if the lookup result equals null" to catch rows with no match, but a failed merge in Power Query returns an empty table, not null. So any row with a genuinely blank join key, like a missing Vehicle Year, threw an error instead of just getting a null key. Twelve rows failed to load because of this. Switching the check to "is the table empty" instead of "does it equal null" fixed all twelve at once.
[Screenshot: the "1 of the loaded queries contained errors, 276 rows loaded, 12 errors" dialog]
[Screenshot: Power BI's auto-generated diagnostic query showing which rows failed]
I also broke one query completely by renaming a step and forgetting that a later step still referenced the old name. Power Query gave me an error saying the import didn't match any exports, which is its way of saying "this step no longer exists." That one taught me to check every step downstream before renaming anything.
[Screenshot: "The import RemoveCustLookup matches no exports" error]
[Screenshot: Model view showing the finished star schema with 8 active relationships]
For dates, I made Order Date the active relationship to the date table, and kept Delivery Date as a second, inactive relationship that I switch on with USERELATIONSHIP whenever I need to look at something from the delivery side instead.
The measures and calculated columns
I tried to make sure every measure existed for a reason, not just to pad out the model. Some of the core ones:
Total Revenue = SUM('Facts table'[Revenue Recorded])
Total Orders = COUNTROWS('Facts table')
Gross Profit = [Total Revenue] - [Total Cost]
Gross Profit Margin % = DIVIDE([Gross Profit], [Total Revenue])
Completed Revenue =
CALCULATE([Total Revenue],
'Facts table'[Payment Status] <> "Cancelled",
'Facts table'[Payment Status] <> "Refunded")
Revenue YoY % = DIVIDE([Total Revenue] - [Revenue PY], [Revenue PY])
One more bug worth mentioning. My first version of Total Orders used a distinct count on Order ID, which undercounted by 21 orders, because Order ID isn't actually unique (remember the UNKNOWN rows and the duplicates). Since each row in the fact table already represents one order, a plain row count was the right fix. Once I changed it, everything downstream that depended on it, like Return Count and Completed Orders, corrected itself automatically.
I also added a few calculated columns to make investigation easier without needing a separate measure for every possible slice: Discount Bracket, Rating Bracket, Price to Cost Ratio, Delivery Lag Days, and a Flag for Investigation column that catches any order with a strange price ratio, a very large discount, or a negative delivery lag. These are the same problems the audit found, just now something a manager can actually filter and click on in the report instead of something buried in my analysis notes.
[Screenshot: the Facts table, 276 rows, keys and cleaned measure columns]
Designing the Executive Dashboard
The first page is meant to answer "how is the business doing" in one glance. It has a title, six KPI cards, all built off "Completed" measures so they're consistent with each other (Completed Revenue, Completed Gross Profit, Gross Profit Margin %, Units Sold, Completed Orders, and Logistics Cost % of Revenue). That last card has conditional formatting, turning red once logistics cost passes 2% of revenue, which is my simple way of flagging something that needs attention rather than just displaying a number. Below that sits a monthly trend chart and two comparison charts, one for branch and one for car make. A year slicer sits next to the title.
[Screenshot: finished Executive Dashboard]
The detail pages
I grouped related topics into pages instead of making one page per business question, since the brief specifically says a single well-designed page can answer several questions at once. I ended up with Sales, Vehicle and Profitability; Branch, Region and Sales Channel Performance; Payments, Delivery and Logistics; and Customers, Discounts and Investigation. There's also a hidden Order Investigation page that you only reach through drill-through.
[Screenshot: one detail page, e.g. Branch, Region and Sales Channel Performance]
Making it interactive
There's a Year slicer on the Executive Dashboard. Right-clicking a branch bar anywhere in the report opens the hidden Order Investigation page, filtered to that branch's flagged orders, showing Order ID, Branch, Car Make, Car Model, Sales Rep, Payment Status, Discount, Price to Cost Ratio, Delivery Lag Days and Returned. Normal cross-filtering between visuals works throughout the report too.
[Screenshot: right-click menu showing "Drill through to Order Investigation"]
[Screenshot: the Order Investigation table filtered to a specific branch, 51 flagged orders]
What I found
The Revenue field, if I'd used it as it was, would have overstated total revenue by about 455 times. That's a good reminder that a field's name tells you nothing about whether the value in it is correct. Every number needs checking before you trust it.
Nairobi HQ has the lowest branch revenue in the dataset, despite being in the capital. That's worth looking into properly, whether it's staffing, stock levels or something about local demand, rather than guessing.
Pending and Partially Paid orders together make up around 40% of both order volume and revenue value. That's a lot of recorded sales that hasn't actually turned into cash yet, which matters more for cash flow than the headline revenue figure suggests.
Bigger discounts don't lead to bigger deals. Average order value is actually lower at 15% plus discount than it is at 0 to 5%. That suggests discounting might be reactive rather than a deliberate strategy.
Trucks and Sedans are the only vehicle types with negative or near-zero profit margin. SUVs carry most of the volume and most of the profit. That's a solid, specific thing to act on for pricing and stock decisions.
What I'd recommend
Look into why Nairobi HQ is underperforming specifically. Compare its lead source mix, staffing and stock availability against Kakamega and Thika Yard, the two strongest branches, before assuming you know the reason.
Review how Trucks and Sedans are priced. Since they're the only vehicle types losing money on average, either the cost side or the pricing side needs a second look, rather than a blanket instruction to "cut costs."
Put a tighter limit on discounts above 15%. Since bigger discounts don't seem to be buying bigger deals, someone giving away that much margin without a clear payoff is worth reviewing, whether that's a policy gap or a few individual exceptions.
I've kept these as things worth investigating rather than proven facts. Correlation isn't causation, and the brief is clear that it doesn't want claims the data can't actually back up.
What I struggled with and what I took away from it
The bugs I described above were the most useful part of the whole project. Every single one of them looked fine at first glance, the model appeared connected, the query loaded without complaint in preview, and only broke once I actually tested it properly: filtering with a slicer, loading the full 276 rows instead of a sample, or renaming something without checking what depended on it. The main thing I'm taking away from this is that a report that builds without an error showing isn't the same thing as a report that's correct. The only way to know the difference is to actually try to break it before you call it finished.
Repository: [link to GitHub repo]
Top comments (0)