JCARS LOGISTICS DATA ANALYSIS WITH POWER BI

Introduction

Jcars is a dynamic automotive retail and distribution company operating across Kenya. Catering to a diverse clientele that includes individual retail buyers, corporate, government entities, and NGOs, the company manages a complex supply chain of vehicle procurement, logistics, and sales. With branches spanning from the bustling hubs of Nairobi and Mombasa to regional yards in Kisumu and Eldoret, Jcars deals in vehicles ranging from everyday sedans and SUVs to heavy-duty commercial trucks.

The Data Quality Challenge

When we first load the raw Jcars_data.csv file into Power BI, it became immediately apparent that we were dealing with a classic "real-world" dataset. We need to clean up the data before we can make any sense of it.

The primary data quality issues included:

Date Anomalies: A chaotic mix of standard text dates ("Aug 29, 2025"), regional formats (30/08/2025 vs 03/20/2026), Excel serial numbers (45671), and outright impossible dates (April 31 2026, 2026-13-04).
Textual Inconsistencies: Car makes and models were plagued by typos and casing issues ("Toyta", "TOYOTA", "Toyota Kenya"). Colors were heavily abbreviated ("Gre", "Bla", "Whi").
Financial Fragmentation: Prices and revenues were recorded in multiple currencies (KES, USD, EUR, ZAR) and mixed notations ("KSh 8,136,000", "9.14M", "$ 46,038.46").
Missing and Invalid Data: Blank Order IDs, negative discounts, and instances where the Delivery Date preceded the Order Date.
To build a reliable Star Schema and accurate DAX measures, we could not simply "clean" the data. I tackled this through m code

1. Date Inconsistencies

Dates are the backbone of any Time Intelligence analysis. Because our dataset contained mixed regional formats, and typos, relying on Power BI’s automatic date detection was impossible. We had to build a custom parser.
The Strategy:
We created a custom M function that evaluates the data type of the cell. IIf it’s text, it attempts to parse it using UK format (DD/MM/YYYY) first, falling back to US format (MM/DD/YYYY).We then added logic to catch transposition typos (like 2026-13-04) and normalize text (like changing "Sept" to "Sep"). Impossible dates (like April 31) are safely converted to null so they don't break the model.

let 
    // 1. Convert to text and trim spaces to prevent type errors
    CleanText = if [Order Date] = null or [Order Date] = "" then null else Text.Trim(Text.From([Order Date])),

    // 2. Check if the text is purely a number (Excel Serial Date)
    IsNumber = try (Number.From(CleanText) > 0) otherwise false,

    // 3. If it's a number, convert Excel serial to Date (Base date: Dec 30, 1899)
    SerialDate = if IsNumber then Date.From(#date(1899, 12, 30) + #duration(Number.From(CleanText), 0, 0, 0)) else null,

    // 4. FIX TYPO: Catch "YYYY-DD-MM" transpositions (e.g., "2026-13-04" -> "2026-04-13")
    FixedTypo = if not IsNumber and CleanText <> null and Text.Length(CleanText) = 10 and Text.Middle(CleanText, 4, 1) = "-" and Text.Middle(CleanText, 7, 1) = "-" then
        let
            Part1 = Text.Middle(CleanText, 0, 4), 
            Part2 = Text.Middle(CleanText, 5, 2), 
            Part3 = Text.Middle(CleanText, 8, 2)  
        in
            if Number.FromText(Part2) > 12 and Number.FromText(Part3) <= 12 then
                Part1 & "-" & Part3 & "-" & Part2 
            else
                CleanText
    else CleanText,

    // 5. NORMALIZE: Change "Sept" to "Sep" to ensure the date parser accepts it
    NormalizedText = if FixedTypo <> null then Text.Replace(FixedTypo, "Sept", "Sep") else null,

    // 6. Parse the text: Try UK format first, then US format
    TextDate = if not IsNumber and NormalizedText <> null then 
                   (try Date.FromText(NormalizedText, [Culture="en-GB"]) 
                    otherwise try Date.FromText(NormalizedText, [Culture="en-US"]) 
                    otherwise null) 
               else null
in 
    // 7. Return the valid date, or null if it's truly invalid
    if IsNumber then SerialDate else TextDate

2. Standardizing Text and Categorical Data

In a Star Schema, dimension tables rely on exact text matches. If a car is listed as "Toyta" in one row and "Toyota" in another, Power Query will create two separate dimension rows, breaking our analytics.
The Strategy:
We standardize the text leaving only one instance and replacing all other values with the correct instance.

3. Unifying Financial Metrics (Prices and Currencies)

Calculating total revenue is impossible if one row is in Kenyan Shillings (KES), another in US Dollars (USD), and another is written as "9.14M".
The Strategy:
We created a two-step process. First, we extracted the raw numeric value by stripping out currency symbols, commas, and text suffixes like "M" (millions). Second, we identified the currency prefix and applied an exchange rate to convert everything into a single base currency (KES).

// Step 1: Extract Raw Numeric Value
let
    RawText = Text.From([Unit Selling Price]),
    // Remove currency symbols and spaces
    CleanedText = Text.Replace(Text.Replace(Text.Replace(RawText, "KSh", ""), "KES", ""), "$", ""),
    CleanedText = Text.Replace(Text.Replace(CleanedText, "USD", ""), "EUR", ""),
    CleanedText = Text.Replace(CleanedText, "ZAR", ""),
    CleanedText = Text.Replace(CleanedText, ",", ""),
    CleanedText = Text.Trim(CleanedText),

    // Handle "M" suffix for millions
    Value = if Text.EndsWith(CleanedText, "M") then 
                Number.FromText(Text.Replace(CleanedText, "M", "")) * 1000000 
            else 
                try Number.FromText(CleanedText) otherwise null
in
    Value

// Step 2: Convert to Base Currency (KES)
let
    RawValue = [ExtractedNumericValue],
    CurrencyPrefix = Text.Start(Text.From([Unit Selling Price]), 3), // Simplified logic for example

    ExchangeRates = [USD = 130, EUR = 140, ZAR = 7], // Example static rates

    ConvertedValue = 
        if Text.Contains(Text.From([Unit Selling Price]), "USD") or Text.Contains(Text.From([Unit Selling Price]), "$") then RawValue * ExchangeRates[USD]
        else if Text.Contains(Text.From([Unit Selling Price]), "EUR") then RawValue * ExchangeRates[EUR]
        else if Text.Contains(Text.From([Unit Selling Price]), "ZAR") then RawValue * ExchangeRates[ZAR]
        else RawValue // Assume KES if no prefix
in
    ConvertedValue

4. Handling Missing and Invalid Data

A common mistake in data cleaning is deleting rows with missing Order IDs or negative discounts to make the dataset "look" clean. However, deleting rows artificially deflates revenue and hides operational flaws.
The Strategy:
Instead of deleting bad data, we preserved the financial transactions but flagged the metadata errors. For missing Order IDs, we generated a unique surrogate key (SalesKey) using an Index Column to ensure every row remained technically unique. For dates where the Order Date occurred after the Delivery Date, we didn't delete the row; instead, we created a boolean flag column. This allows management to see the revenue, but filter out the bad data when calculating precise operational KPIs like "Average Delivery Time."

Modelling Jcar Data

Once the data was clean, we now turn it into a Star Schema. This optimizes the PowerBI for maximum compression and query speed.
The Fact Table (Jcar_merge_facts): We established the grain of the model at the individual transaction level. The Fact table was stripped of all descriptive text and populated strictly with Foreign Keys linking to the dimension tables and numeric metrics (Units Sold, Unit Cost, Discount, Revenue, Logistics Cost).
The Dimension Tables: We turned descriptive context into highly optimized dimension tables by removing duplicates and creating unique IDs as PK to be linked as FK in the Facts table

Dim_Vehicle: Granular vehicle specifications (Make, Model, Year, Fuel, Transmission, Color).
Dim_CusLocation: Gives the customer location (Region → County → City).
DimBranch, DimSalesRep, DimCustomer, DimPayment: These give the operational and demographic contexts of the business helping determine key metrics that are important in gauging the performance of different facets in Jcar data in isolation and as a combination.

The primary keys in the various dimensions tables were linked were linked through merging. After cleaning the facts table I duplicated it such that the table that I merged the dimensions into was delinked from the facts table I had used to reference when creating the dimensions table. This is because merging to the file you referenced from will throw can error as you cannot merge a file you have referenced from in BI.
For all the dimensions table apart from Dim_Customer I used left outer join linking all matching columns facts table with the respective dimensions table while ensuring the keys FK:PK are also matching. I then deleted the matching columns from the facts table as it is now represented by the foreign key.

Cardinality and Direction

We configured all relationships as One-to-Many (1:*) with Single-Direction filter flow (from Dimension to Fact). This prevents ambiguous filter paths and ensures predictable DAX behavior. This is apart from the Dim_Customers which has a relationship of 1:1

Business Intelligence Logic

This is the comprehensive summary of the Jcars business logic, structured around the logical business analysis. This framework bridges the gap between raw data and strategic decision-making, demonstrating how specific analytical work drives actionable insights and recommendations. We use DAX calculations to show the metrics of various financial statements and use our expertise to recommend actionable measures for the business.

• Total Revenue:
  Total Revenue = SUM(FactSales[Revenue Recorded])

• Total Gross Profit:
  Total Gross Profit = SUMX(FactSales, FactSales[Units Sold] * (FactSales[Unit Selling Price] - FactSales[Unit Cost]))

• Gross Profit Margin:
  Gross Profit Margin = DIVIDE([Total Gross Profit], [Total Revenue], 0)

• Total Goods Sold:
  Total Units Sold = SUM(FactSales[Units Sold])

Dashboard

Findings, Insights and Recommendations

1. Key Findings and Metrics

1. Vehicle Performance (Make and Model)
The highest revenue by Make is Toyota. It stands out as the unparalleled market leader, generating approximately 0.65bn to 0.7bn Ksh which is roughly 44% - 47% of the total revenue. It also generates the highest absolute Gross Profit.
Similarly the highest return by car model is the Toyota Harrier, proving to be the best selling model with 34 units sold, followed closely by the Toyota Fit 30 units and Toyota Prado TX 29 units.
However there happens to be a high margin anomaly. While Toyota leads in volume and revenue, the Total Revenue and Total Gross Profit by Car Make chart reveals that Volkswagen and BMW have disproportionately high gross profit margins relative to their revenue. Purporting quite a high profit margin per unit sold.hhhmmm

2. Lead Source Performance
Our highest Revenue Lead Source comes Instagram. Performing in the generation of 271.03M (18.26%) of total revenue.
Social Media has dominated bar none with Facebook as our second highest at 230.57M (15.54%). Combined, Instagram and Facebook drive 33.8% of all company revenue.
Other Channels: Website 11.78%, Referrals (11.64%), and Phone Calls 11.63% perform relatively evenly, while Corporate Tenders contribute the least at 9.41%.

3. Customer & Payment Analysis
The top three revenue drivers Dealers (23.13%), Government (22.70%), and NGOs (20.22%). Individual customers contribute the least at 15.11% 224.3M.
Payment collection issues: The Total Revenue by Payment Method and Payment Status chart highlights a major operational risk. Across all major payment methods (M-Pesa, Cash, Cheque, Bank Transfer), there are massive blocks of "Pending" (purple) and "Partially Paid" (orange) statuses. An example is M-Pesa and Cash both have roughly 20-25% of their revenue stuck in pending or partially paid states.

4. Regional & Branch Performance
Top Branches: Kakamega is the highest performing branch (255M), followed by Thika 247M and Nakuru 212M .
Underperforming Branch: Nairobi is the lowest-performing branch, generating only 78.53M, which is roughly 30% of Kakamega's performance despite being in the capital city. This may need some investigation.

2. Strategic Insights

A. The "Toyota Reliance" Risk: Relying on a single make for nearly half of revenue exposes JCars to supply chain shocks or market saturation. Look to diversify into other brands that may spread the risk even though the toyota seems to be "darling"

B. Cash Flow impeding dilemma: The high volume of "Pending" and "Partially Paid" transactions indicates that while sales teams are good at closing deals, the collections/finance team is struggling to finalize payments. This artificially inflates revenue figures while starving the company of actual working capital.

C. Marketing ROI speaks for itself. Ancient methods such as walk-ins, phone calls and expensive B2B efforts like the Corporate Tenders are underperforming compared to social media marketing Ig and Fb, which bring in majority of the revenue.

D. The Nairobi anomaly in it's severe underperformance suggests either intense local competition, poor branch management, or a mismatch in inventory eg stocking cars that don't appeal to the Nairobi demographic, or part of the cars that are unaccounted for.

Recommendations

Procurement and inventory segment priorities

1. Maintain Toyota Stock and continue prioritizing the procurement of Toyota Harriers, Prado TXs, and Fits, as they are the proven volume and revenue drivers.
2. Prioritize increasing the inventory of Volkswagen and BMW, to capitalize on high margin brands the dashboards show they yield exceptionally high gross profit margins. Selling just a few more units of these could significantly boost the company's revenue without needing massive volume.
3. Diversify: Slowly introduce more Mazda and Mercedes-Benz inventory to capture the mid-to-high-end market that isn't buying Toyota.

Marketing & Sales Strategy

1. Shift marketing budget away from low-performing channels and heavily invest in Instagram and Facebook. Since they generate 34% of revenue, JCars should implement targeted ad campaigns specifically for the Toyota Harrier and Prado TX on these platforms.
2. Incentivize referrals as they generate 11.64% of revenue . Implementing a formal Customer Referral Bonus program could easily push this channel into the top 3.

Operational & Financial Improvements

1. I recommend having a collection taskforce as a course of action. Management must immediately address the "Pending" and "Partially Paid" backlog. Implement strict SLAs (Service Level Agreements) for sales reps to follow up on pending payments within 48 hours of delivery. Consider tying sales commissions to collected revenue rather than booked revenue.
2. Audit Nairobi branch: Conduct an immediate operational audit of the Nairobi branch. Compare its inventory mix, staff performance, and lead response times against the Kakamega branch to identify why the capital city branch is lagging so severely.
3. Boost Individual Retail: Since Individual customers make up the smallest segment (15.11%), JCars should consider creating Retail-Only weekend promotions or flexible micro-financing options to attract individual buyers who may not have the bulk capital of Dealers or NGOs.

Conclusion

This project demonstrates the critical journey from raw, unstructured data to strategic business intelligence for Jcars. By rigorously cleaning the dataset and creating a robust star schema, we transformed unreliable records into a high-performance analysis. Jcars is now equipped to optimize profit margins, streamline its supply chain, and drive sustainable growth in a competitive automotive market in the Kenya market.