From Raw Data to Business Decisions: Building a Power BI Solution for JCars Logistics

JCars Logistics imports, sells, and delivers vehicles across regions in Kenya. I was handed a single raw flat file - 32 columns, 277 rows - describing sales orders, customers, vehicles, branches, payments, deliveries, logistics costs, returns, cancellations, and customer experience. The task: turn it into a reliable, interactive Power BI solution that helps management actually understand how the business is performing.

No cleaning had been done. Nothing was standardized. That was the point - the assessment was as much about finding the problems as fixing them.

This article walks through that journey: investigation, cleaning, modelling, DAX, dashboard design, and what I found along the way.

Step 1: Understanding the Data Before Touching It

Before writing a single transformation, I established the grain: one row = one vehicle sales order, identified by Order Id. The columns broke down into four natural groups - order/transaction details, customer details, vehicle details, and location/branch details. That grouping is what eventually became the star schema (more on that later).

Step 2: The Data Quality Audit

This dataset earned its "raw" label. A few of the more interesting problems:

Order IDs weren't really IDs. They arrived with inconsistent prefixes (LC, LCL-, ORD, plain numbers), some blank, some N/A, and - worse - several duplicated across completely unrelated rows. I rebuilt the column entirely using an incremental index (starting at 1000, prefixed ORD-), landing on 276 clean, unique order identifiers.

#"Added Index" = Table.AddIndexColumn(#"Removed Top Rows", "Index", 1000, 1, Int64.Type)

#"Inserted Prefix" = Table.AddColumn(#"Added Index", "Order Id", each "ORD-" & Text.From([Index]), type text),

Dates were a mess of formats. Order Date and Delivery Date mixed Excel serial numbers, multiple locales (en-US vs en-GB), plain-text errors like #DATE!, and outright invalid dates (2026-13-04, 31/02/2026). I built a try...otherwise parsing chain that attempts each known format in sequence and falls back to null only when nothing matches - rather than guessing.

Branches hid behind 64 different labels. Yards, HQs, city names, and abbreviations were all technically "the same place" under different labels. I cross-referenced the Sales Rep assignments - reps listed under "Thika," "Thika Yard," and "Thika Branch" turned out to be the exact same people - which confirmed these were naming variants of 8 actual branches, not distinct locations.

A typo pattern nobody would catch by eye. Several Sales Rep names had the number 1 substituted for the letter I (e.g., At1eno instead of Atieno) - likely a font/OCR-style substitution upstream. Combined with double-spaced names creating false duplicates, the rep list initially showed 20 "distinct" values that were really only 10 people. The code block below shows how the cleanup for the column was done.

CleanSalesRep = (raw as nullable text) as nullable text =>
        let
            v0 = Text.Trim(Text.Clean(raw ?? "")),
            v1 = Text.Replace(v0, "1", "i"),      
            v2 = Text.Proper(v1),
            v3 = Text.Replace(v2, "  ", " "),
            v4 = Text.Trim(v3)
    in
        if v4 = null or v4 = "" then "Unknown"
        else if v4 = "Mercy" then "Mercy Atieno"
        else if v4 = "Kevin" then "Kevin Mwangi"
        else if v4 = "Grace" then "Grace Njeri"
        else if v4 = "Mary" then "Mary Wanjiku"
        else if v4 = "Peter" then "Peter Kiptoo"
        else if v4 = "Faith" then "Faith Achieng"
        else if v4 = "Daniel" then "Daniel Kimani"
        else if v4 = "Aisha" then "Aisha Mohamed"
        else if v4 = "Brian" then "Brian Otieno"
        else if v4 = "Samuel" then "Samuel Mutua"
        else v4,

Discounts exceeding 100%. A handful of rows showed discount values like 1.2, which could mean 120% (a data error) or a misplaced decimal meant to be 12%. Rather than guess - either interpretation could be wrong, and guessing wrong is worse than leaving it blank - these were nullified and flagged for follow-up with the data source, rather than silently "corrected" into a number that might be equally incorrect.

Suspicious customer ages. The value 121 appeared three times, and -5 appeared three times too - clear signs of placeholder or default values rather than real ages. Since there was no date-of-birth field to cross-check against, these (along with 0) were treated as invalid and nullified rather than assumed.

Step 3: Standardizing Currency

Monetary columns (Unit Selling Price, Unit Cost, Discount, Delivery Fee, Logistics Cost, Revenue Recorded) arrived as text, mixing four currencies - KES, USD, EUR, ZAR - identified inconsistently by prefixes (Ksh, $, €, R) and magnitude suffixes (K for thousand, M for million).

Any value with no explicit currency marker was assumed to be KES. Everything else was converted using one consistent exchange rate set applied across the whole project:

Currency Rate to KES
USD 129.50
EUR 135.50
ZAR 7.10

_Source: https://www.investing.com/currencies _

Step 4: Cleaning in Power Query - and a Performance Lesson

Each problematic column got its own dedicated M function - dates, categorical text, monetary values, discounts, ratings - rather than one-off inline fixes scattered everywhere.

One mistake worth sharing: early on, I cleaned categorical columns (Region, County, Branch, Sales Rep, Lead Source, Car Make, Car Model) using long chains of Table.ReplaceValue steps - one per misspelling. Each of those steps re-scans and rematerializes the entire table, which meant the query got progressively slower as more steps piled up.

The fix was switching to a single Table.TransformColumns call per column, with the mapping logic expressed as one if...then...else if chain inside it:

powerquery
#"Cleaned Region" = Table.TransformColumns(#"Previous Step", {
    {"Region", each
        let v = Text.Proper(Text.Trim(_)) in
        if v = "Nbi" or v = "Nrb" or v = "Nairobii" then "Nairobi"
        else if v = "Rift" or v = "Rift-Valley" then "Rift Valley"
        else if v = "Msa Region" or v = "Cost" then "Coast"
        else if v = null or v = "" then null
        else v
    , type text}
})

Same result, a fraction of the applied steps, and a noticeably faster refresh.

Step 5: From Flat File to Star Schema

A flat table repeats every customer's, vehicle's, and branch's details on every single transaction row - that's redundant, harder to filter efficiently, and makes relationships harder to express. So the cleaned data was restructured into a star schema:

Fact_Sales - one row per order, holding the measures and foreign keys
Dim_Vehicle - Car Make, Car Model, Vehicle Type, Vehicle Year, Fuel Type, Transmission, Color
Dim_Customer - Customer Name, Customer Type, Customer Age
Dim_Branch - Region, County, City, Branch
Dim_SalesRep - Sales Rep
Dim_Date - a DAX-generated calendar table

Two modelling decisions worth explaining:

Why Customer Age lives in Dim_Customer, not Fact_Sales. Since there's no true Customer ID in the source data, Name + Type + Age was used as a proxy identity key for deduplication. This is an honest limitation, not a hidden one - two different customers sharing all three attributes would incorrectly merge into one customer record. Worth flagging to management if customer-level analysis becomes more important later.

Why some fields stayed in the fact table. Lead Source, Payment Method, Payment Status, Delivery Status, Returned, Customer Rating, and Review Count describe the transaction, not a reusable entity - splitting them into tiny standalone dimensions would add joins with no analytical payoff.

Building the fact table meant merging each dimension's descriptive columns back in as a lookup and keeping only the surrogate key. The one gotcha that cost some debugging time: Power Query's merge treats null = null as a non-match. If a descriptive column had blanks on both the fact and dimension side, that merge would silently fail for those rows. The fix was ensuring consistent non-null placeholders (e.g., "Unknown") flowed through from the cleaning stage, and spot-checking each merge with a temporary Table.RowCount() column before trusting it.

Step 6: DAX Measures

With a proper model in place, measures could finally express real business logic instead of relying on default aggregations. A sample:

dax
Total Revenue (Calculated) = 
SUMX(Fact_Sales, Fact_Sales[Units Sold] * Fact_Sales[Unit Selling Price] * (1 - Fact_Sales[Discount]))

Revenue Variance = [Total Revenue (Calculated)] - [Total Revenue (Recorded)]

Gross Profit Margin % = DIVIDE([Gross Profit], [Total Revenue (Recorded)])

Return Rate = 
DIVIDE(
    CALCULATE(DISTINCTCOUNT(Fact_Sales[Order Id]), Fact_Sales[Returned] = "Yes"),
    [Total Orders]
)

Revenue Variance is doing double duty as a validation check - comparing the recorded revenue against an independently calculated figure lets discrepancies surface naturally instead of blindly trusting one number.

Step 7: The Executive Dashboard

Page 1 had one job: let a manager understand "how is the business doing?" in under a minute, without needing anything explained.

It's organized into five zones - KPI cards up top (Revenue, Units, Gross Profit, Margin, Orders), a chronological revenue trend line, Top-10 bar charts for Branches and Car Makes, a regional revenue map, and a payment-status breakdown by value.

One small addition made a disproportionate difference: a conditionally-formatted Return Rate card - green under 30%, red above. It sounds trivial, but it turned into a genuine debugging lesson: even though the card displays as a percentage, Power BI's conditional formatting engine evaluates the underlying decimal (0.3), not the display value (30). My first attempt used whole-number thresholds (30 instead of 0.3), which meant the "red" rule was mathematically impossible to trigger and the "green" rule matched everything. The card looked "done" and was quietly wrong the entire time. Worth double-checking on any rate-based conditional formatting, not just this one.

Step 8: What I Found - Key Insights

• The return rate is unusually high overall with BMW standing out sharply. 32% of all orders were returned. Nakuru Branch leads (16 of 33 orders). Whether it is a problem with Nakuru, it is an issue that needs investigation.

• Digital lead sources bring high-value customers than manual ones. Instagram orders average Kes 6.5 million and Facebook Kes 5.9 million compared to Walk-Ins at Kes 3.1 million. This suggests marketing spend on social channels may be attracting a meaningfully higher-spending customers segment, worth validating with a large sample before reallocating budget on it alone.

• Customer ratings and revenue are unrelated. The correlation between Customer Rating and Revenue Recorded is -0.02. Happier customers aren't spending more, and higher-value customers aren't necessarily happier.

Recommendations for Management

• Open a formal quality investigation into Nakuru Branch operations. The return rate at Nakuru Branch is at 48.5%, and highest of any branch. Conduct an audit into the delivery handling and customer communication

• Reassess how marketing spend is allocated across lead sources. Since our analysis shows social media sourced orders are higher compared against methods such as walk-ins, testing could be done to assess a shift towards social channels,and could be done over a long period before a permanent reallocation is done.

• Audit and correct the pricing model for Toyota. Toyota is the highest revenue make with around 560 million (0.45% margin), while Subaru, returns a 6.96 % margin. Management should investigate whether the current selling prices cover the true cost to deliver.

Closing Thoughts

The hardest part of this project wasn't the DAX or the visuals - it was resisting the urge to "fix" ambiguous data by guessing. Nullifying an unclear value and documenting why is a more honest analytical decision than quietly inventing a number that looks plausible. That discipline - document the assumption, apply it consistently, and let management know where the data itself sets the limit - is the difference between a dashboard that looks finished and one that's actually trustworthy.