An Introduction
JCars Logistics sells and delivers vehicles across Kenya, and like a lot of growing businesses, its data told a story that hadn't been organized into anything management could actually use. I was handed one raw, uncleaned CSV export with 32 columns, no documentation, no guarantee that any value in it was correct, consistent, or even in the right currency and I was asked to turn it into a Power BI solution management could open on their own and understand.
This is the story of that process: what I found wrong with the data, how I decided to fix it, and where the analysis ended up.
Step 1: Understanding What I Was Actually Looking At
Before touching a single transformation, I spent time just reading the data. The grain turned out to be one row per vehicle sales transaction but the file mixed customer information, vehicle details, branch and sales rep identifiers, and transaction-level facts all into a single flat table. I knew this wasn't going to stay a single table by the time I was done, it needed to become a proper star schema.
Step 2: The Data Quality Audit
I went column by column looking for problems, and found far more than the usual typos-and-missing-values list:
1.Spelling chaos across nearly every categorical field, "totoya," "toyta," and "toyotakenya" all needed to resolve to "Toyota", "cental" needed to become "Central." I built explicit mapping tables in Power Query for every affected column rather than trying to fuzzy-match my way through it, since a hardcoded map is auditable and a fuzzy match isn't.
2.Dates in at least three different formats- Excel serial numbers, UK-style text dates, US-style text dates, plus literal strings like "#DATE!" and "N/A". One parsing function handled all of them, falling back to null (and a data quality flag) for anything it couldn't confidently interpret.
3.Free-text numeric fields - a discount column that sometimes said "fifteen" or "ten percent" instead of a number, a units-sold column with "one" and "two cars" mixed in with actual integers. Each got its own small parsing function.
None of this was hard to spot. What came later was even harder.
Step 3: The Currency Problem I Almost Missed
The assessment brief warned that monetary values might be recorded in different currencies. I didn't find any currency markers on a first pass through the raw text, no obvious "USD" jumping out, so I built my initial cleaning pipeline assuming everything was already in Kenya Shillings.
It was validation, not inspection, that caught the real problem. When I cross-checked my computed Revenue measure against a separately-recorded Revenue Recorded field already present in the data, a cluster of transactions showed the computed figure was consistently 130 to 150 times the recorded one. That's not a rounding error, that's the signature of a currency conversion that never happened, since 130-150 sits squarely in a plausible KES/USD exchange rate range.
Going back into the raw pre-cleaning text confirmed it, several rows genuinely had USD or $ prefixes sitting in front of the numbers, which my initial parsing function was silently stripping out as if they were just formatting noise. Worse, one row displayed as a literal ? character in the price field, a field so mangled it wasn't even parsing correctly, which turned out to be a CSV text-encoding mismatch, fixed by explicitly declaring the file's encoding when reading it in Power Query.
The fix went into a single shared parsing function so it applied consistently everywhere money was calculated:
fnNum = (v as any) as nullable number =>
let
t = Text.Upper(Text.Trim(Text.From(v) ?? "")),
isUSD = Text.Contains(t, "USD") or Text.Contains(t, "$"),
isM = Text.EndsWith(t, "M"),
digitsOnly = Text.Combine(List.Transform(Text.ToList(t), each if List.Contains({"0","1","2","3","4","5","6","7","8","9",".","-"}, _) then _ else "")),
n = try Number.From(digitsOnly, "en-US") otherwise null,
scaled = if n = null then null else if isM then n * 1000000 else n,
converted = if scaled = null then null else if isUSD then scaled * ExchangeRateUSD else scaled
in
converted
The lesson here mattered more than the fix itself: a clean-looking column doesn't mean a correct one. The currency problem was invisible until I deliberately cross-checked two independently derived figures against each other and asked why they disagreed.
Step 4: One Outlier, One Very Specific Fix
Somewhere in the middle of validation, one vehicle's selling price stood out: KSh 122,420,000, against a perfectly ordinary cost of KSh 9,382,000. That's a 13x markup, nowhere near the currency-mismatch pattern I'd just found, and far outside anything plausible for a single vehicle sale.
Rather than deleting the row, I treated it as a probable data-entry error, likely an extra digit and corrected it to KSh 12,242,000, which produces a sensible margin against its cost. That correction is documented and traceable to a specific Order ID, not a silent edit.
Step 5: Building the Model
With the cleaning genuinely settled, I split the flat table into a star schema: FactSales at the center, with DimCustomer, DimVehicle, DimBranch, DimSalesRep, and a custom DimDate calendar table around it. None of the dimension entities had a natural unique key in the source data, so each got a surrogate ID generated via a Power Query Index column — deduplicated before indexing, which I learned the hard way matters: index first and dedup second, and the dedup step does nothing, since every row already looks "unique" by virtue of its own index number.
Step 6: DAX, and a Few More Landmines
Building out the measures surfaced a batch of small but very findable bugs, mostly typos against actual column names (Units instead of Units Sold, OrderID instead of Order ID), a couple of measures referencing other measures that didn't exist yet, and one Gross Profit formula that had accidentally been written as if it were a different measure entirely. None of these were conceptually hard, they were the kind of error that autocomplete-selecting a column name instead of typing it from memory would have prevented outright. More interesting was validating Total Orders. My first instinct was DISTINCTCOUNT(Order ID) - until I found that 19 different transactions all shared a literal placeholder value of "Order ID Missing" (assigned to any row where the original ID was blank), which meant a distinct count was quietly collapsing 19 real orders into one. Since the dataset's grain is one row per order regardless of what the ID field says, COUNTROWS(FactSales) was the correct, simpler measure all along.
Step 7: Validation Turned Up Even More
Running a full validation pass (row counts, referential integrity, cross-checks against independently recorded fields) surfaced:
1.16 transactions where the delivery date was recorded before the order date, which is logically impossible, and with no consistent pattern (gaps ranged from 2 to 315 days) that would let me infer the "real" date. I nulled these and let the existing data-quality-flag logic pick them up automatically, rather than inventing a corrected date I had no evidence for.
2.A cluster of transactions where Unit Selling Price was implausibly below Unit Cost, some explained by the currency issue above, some by a documented zero-fill default for originally-missing prices, and a genuine remaining handful worth flagging as exceptions rather than "fixing."
Step 8: From Model to Management Tool
The Executive Dashboard (Page 1) was built to answer one question in five seconds: how is the business doing? KPI cards for revenue, profit, margin, units, orders, and return rate; a monthly revenue trend; branch and make comparisons; and slicers for date, branch, and vehicle type.
From there, three detail pages let management go deeper; sales and vehicle profitability, branch/rep/customer performance, and logistics/payments/exceptions connected by a drillthrough from customer records into a filtered exceptions view, and a custom tooltip for quick context without leaving the page.
What I Would Tell Management
A handful of findings stood out enough to act on, not just report:
1.The return rate sits at roughly 32% - high enough to investigate by branch, vehicle year, and sales rep before assuming a single cause.
2.The manually recorded revenue field is unreliable and disagreed with the computed figure on a large share of transactions - future reporting should rely on the computed figure exclusively.
3.Neither the currency mixing nor the price outlier would have been caught by inspection alone - both surfaced through validation, cross-checking computed values against independently recorded ones. That's the single biggest process recommendation I'd make, to build the cross-check into routine reporting, not just into a one-off cleaning project.
Closing Thought
The technical skills here; Power Query, DAX, star schemas, were the easy part. The harder discipline was resisting the urge to treat a clean looking dataset as a correct one, and instead building in the habit of asking "does this number agree with a different way of arriving at the same number?" That question is what actually found the currency problem, the outlier, and the unreliable revenue field, not a sharper eye on the raw file.
Top comments (0)