DEV Community

Cover image for JCars Logistics: From Raw Data to Actionable Insights Using Power BI
Clarence Gatama Chege
Clarence Gatama Chege

Posted on

JCars Logistics: From Raw Data to Actionable Insights Using Power BI

#analytics #data #database

JCars Logistics is a Kenyan vehicle sales and logistics business operating from a main yard in Nairobi and seven other branches. It sells vehicles to individuals, dealers, corporates, government bodies and NGOs and arranges delivery to customers.

Understanding the Dataset
The raw data came as a single flat CSV export Jcars_data.csv containing 32 columns. The grain of the dataset: a row represents a sale made by Jcar with the following fields recorded for each sale:

Item Detail
Source file Jcars_data.csv
Columns 32
Grain One row per order (identified by Order ID)
Customer fields Name, Type, Age, Rating, Review Count
Location fields Region, County, City, Branch
Vehicle fields Make, Model, Vehicle Type, Year, Fuel Type, Transmission, Color
Money fields Unit Selling Price, Unit Cost, Discount, Delivery Fee, Logistics Cost, Revenue Recorded
Process fields Order Date, Delivery Date, Payment Method, Payment Status, Delivery Status, Returned, Lead Source, Sales Rep, Unit Sold

DATA QUALITY AUDIT

  1. Missing header - header values were saved in first row.
  2. Missing Values - all columns except Customer Type, County, City, Branch, Sales Rep, Car make, car model, Fuel Type, Transmission, Color, Unit Selling Price, Payment Method, Payment Status and Revenue Recorded have blank values.
  3. Inconsistent formatting - the two date columns , order date and delivery date have different date formats ,for example, 03/20/2026, 10-Feb-25, 19/01/2025. Currency columns also have inconsistent formatting with values having different currencies for example: USD, KES, KSH, ZAR and others not specified. Text columns such as Customer name, Customer Type etc also have formatting issues. Some text values are in proper case, others are fully uppercase and others are fully lowercase.
  4. Inconsistent Categories - examples of inconsistent categories in this dataset include government, Government, GOVERNMENT, govt, GOVT all representing the same Customer Type. Such cases are also there in other columns like Region, County, City, Branch, Sales Rep, Lead Source, Car Make, Car Model etc
  5. Possible Outliers - in the dataset there is an entry of a customer whose age is 121

DATA CLEANING
1. Headers- my first cleaning step was setting the first row as the headers. This can be done in Power Query Transform Tab under the Table Subgroup. The screenshot below show the before and after transformation.

Before:

Power bi screenshot showing column names in first row

After:

Power bi screenshot after first row values were set to headers

2. Formatting - Most text columns have values in different formats e.g, all uppercase, all lowercase and others in proper case. I converted all text columns to proper case on power query by right clicking each text column and selecting Capitalize Each Word under Transform.

3. Inconsistent Categories - there are columns with different values representing the same thing e.g, government, Government, GOVERNMENT, govt, GOVT in Customer Type column. I used Replace Values to replace the different values with a single value, for example for Customer Type government I replaced all values representing government with a common value Government.

4. Category columns

Columns such as Car Make, Region, Branch, Sales Rep, Payment Status and Delivery Status were cleaned with:

  • Trim / Clean (Transform tab) to remove extra spaces and non-printable characters.
  • Replace Values, run once per known misspelling or abbreviation (for example totoya - Toyota, nbi - Nairobi).
  • For columns with partial entries (Sales Rep recorded as just Grace instead of Grace Njeri), Replace Values was used again with "Match entire cell contents" checked, so the fix only applied to standalone first names and did not corrupt rows that were already correct full names.
  • Remaining unmatched or placeholder values (blank, "na", "unknown", "tbd") were replaced with Unknown.

5. Numeric and currency-style columns
Columns Unit Cost, Unit Selling Price, Delivery Fee, Logistics Cost and Revenue Recorded were mixed KES, Ksh, USD, EUR, ZAR, R and an unrecognised ? symbol. Exchange rates used in cleaning were: USD - KES: 129.45,
EUR - KES: 147.28,
ZAR - KES: 7.85
Here is how they were standardised to KES:

  1. Detect currency from the prefix before stripping symbols, using a Conditional Column (Rate Multiplier): USD/$/? - 129.45, EUR - 147.28, ZAR/R - 7.85, KES/KSh/no label - 1.
  2. The ? symbol did not have a clearly identifiable currency. To determine the most appropriate currency, the affected values were converted using the USD, EUR, and ZAR exchange rates. The USD conversion produced results that were reasonable and fell within the expected range for each row.
  3. Another Conditional Column was used to flag rows with a M suffix. For these rows the new column stored 1,000,000 as a multiplier, while all other rows were set to 1.
  4. Replace Values was used to strip the currency labels (USD, EUR, ZAR, R, ?, ,) from the original column, leaving just the raw digits, which was then converted to a numeric type.
  5. Combine the columns. The three columns (the cleaned numeric value, the Rate Multiplier, and the millions multiplier) were then combined by multiplying them together (product) producing the final standardised value in KES. ** 6. Word-based numbers Columns like Customer Age (thirty) and Discount (fifteen, ten percent) were handled with Replace Values, mapping each specific word to its digit equivalent, before the column was converted to a numeric type.

6. Validity ranges

Enforced with Conditional Column, setting any out-of-range value to null:

Column Valid range
Customer Age 18–100
Customer Rating 1–5
Discount 0–50%

7. Sales Rep specific fix

Some rep names had a stray 1 in place of the letter i (for example Fa1th for Faith). This was corrected with a Replace Values pass (1 to i) run before the name-matching replacements, and double spaces between first and last names were collapsed with a second Replace Values pass (two spaces - one space).

8. Derived columns
Expected Revenue was added as a Custom Column:

[Units Sold] * [Unit Selling Price] * (1 - [Discount]) + [Delivery Fee]
Enter fullscreen mode Exit fullscreen mode

9. Date Column Cleaning
Dates arrived in mixed formats and some had been stored as whole numbers (Excel serial dates). The steps below were used to clean Order Date & Delivery Date:

  1. Mixed date formats: a small number of dates were in mm/dd/yyyy instead of dd/mm/yyyy. These were identified and corrected manually using Replace Values to swap them into the consistent dd/mm/yyyy format.
  2. Whole-number (serial) dates: to isolate these, the date column was duplicated. The duplicate was converted using Change Type - Whole Number, which turned every genuine date-text value into an error/null, leaving only the true serial numbers intact.
  3. That duplicate was then converted using Change Type - Date, which correctly resolved the remaining serial numbers into proper dates.
  4. A Conditional Column combined the two: where the original cleaned date column had a value, it was used; where it was null, the converted duplicate (now holding the resolved serial dates) was used instead. This conditional column became the final cleaned date column.

Assumptions and Business Rules

  • Grain is one order per row;
  • Expected Revenue = Units Sold × Unit Selling Price × (1 − Discount) + Delivery Fee.
  • Discounts above 50% are treated as data errors.
  • Values without a currency label are assumed to be KES.
  • Ambiguous ? currency values were assumed to be USD.

Data Modelling Approach

The flat table was reshaped into a star schema: one fact table of measurable events surrounded by dimension tables of descriptive attributes. Benefits: smaller model (repeated text stored once), faster filtering, and simpler DAX.

10. Important DAX Measures 

Total Revenue = SUM(Fact_Sales[Revenue])
Enter fullscreen mode Exit fullscreen mode

Aggregates the pre-computed order revenue; responds to whatever dimension is on the visual.

Total Cost = SUMX(Fact_Sales, Fact_Sales[Unit Cost] * Fact_Sales[Units Sold])
Enter fullscreen mode Exit fullscreen mode 
Total Gross Profit =
SUMX(Fact_Sales, Fact_Sales[Revenue] - (Fact_Sales[Units Sold] * Fact_Sales[Unit Cost]))
Enter fullscreen mode Exit fullscreen mode 
Gross Profit Margin % = DIVIDE([Total Gross Profit], [Total Revenue])
Enter fullscreen mode Exit fullscreen mode 
Units Sold Total = SUM(Fact_Sales[Units Sold])
Enter fullscreen mode Exit fullscreen mode 
Avg Customer Rating = AVERAGE(Fact_Sales[Customer Rating])
Enter fullscreen mode Exit fullscreen mode 
Total Logistics Cost = SUM(Fact_Sales[Logistics Cost])
Enter fullscreen mode Exit fullscreen mode 
Net Logistics Margin = SUM(Fact_Sales[Delivery Fee]) - SUM(Fact_Sales[Logistics Cost])
Enter fullscreen mode Exit fullscreen mode

Calculated column example

Age Band =
SWITCH(TRUE(),
    ISBLANK(Dim_Customer[Customer Age]), "Unknown",
    Dim_Customer[Customer Age] < 25, "18-24",
    Dim_Customer[Customer Age] < 35, "25-34",
    Dim_Customer[Customer Age] < 45, "35-44",
    Dim_Customer[Customer Age] < 55, "45-54",
    "55+")
Enter fullscreen mode Exit fullscreen mode

11. Executive Dashboard Design

Purpose: monitoring, not investigation.This is to allow stakeholders to quickly understand the organisation’s overall performance at a glance.

Detailed Report Structure

Key Findings

  • Kakamega sold the most units, collected the highest revenue, and had the most fully paid units.
  • Thika had the most refunded units.
  • Only Athi River, Kakamega, Mombasa and Nakuru had a positive gross profit margin.
  • Sales were heavily dominated by Toyota (144 units), with Mazda second at 69 units, less than half.
  • Toyota had the highest gross profit (KSh 23.26M); Isuzu had the largest gross loss (KSh -23.06M).
  • Peter Kiptoo sold the most units of the most profitable vehicle.
  • Mitsubishi, Mercedes, Mazda, Isuzu and BMW had more than 50% of units not fully paid.
  • Orders with the most cancelled payments were mainly handled by Daniel Kimani, Grace Njeri and Faith Achieng.
  • Kakamega and Thika had the highest delivery fees and logistics costs.
  • Delivery fees are KSh 2.99M below logistics costs.
  • Customer view: the 55+ age band generated the highest revenue (KSh 0.36bn); the top customer was John Kariuki (KSh 53M); Individual customers had the highest share of fully paid units.
  • Volume does not equal profit. Most branches sell at a negative margin, so growth without margin discipline increases losses.
  • A large share of units across five makes is not fully paid, creating collection risk.
  • Delivery is a cost centre. Fees recover less than logistics spend.
  • The best branch is also expensive to serve. Kakamega leads on sales and collection but also on logistics cost.

Recommendations

  1. Tighten payment collection — set up minimum deposits for each order and do payment follow-ups.
  2. Review Isuzu and loss-making branches — check pricing and total cost incurred.
  3. Reprice delivery either based on distance or vehicle type to close the KSh 2.99M logistics gap.
  4. Investigate Thika's high return rate and improve pre-delivery checks.
  5. Coach sales reps on cancellation-heavy accounts.
  6. Replicate Kakamega's sales practices elsewhere while monitoring its cost-to-serve.
  7. Improve data capture — mandatory cost fields, single currency at entry and age range validation.

20. Conclusion

The original flat file contained inconsistencies that needed to be addressed before meaningful analysis could be performed. After cleaning and validating the data, it was transformed into a structured star schema with reusable DAX measures and an interactive dashboard. The analysis shows that JCars Logistics generates strong sales but faces challenges from low margins across many branches, a significant share of unpaid units, and delivery costs that are not fully covered by delivery fees. Future analysis can be strengthened through better data collection, including accurate unit costs, reliable customer identifiers and consistent currency records.

The full project, including the Power BI file and supporting files are available on GitHub: Github Repo

Top comments (0)