From Raw Vehicle Sales Data to Business Intelligence: Building the JCars Logistics Power BI Solution

Introduction

JCars Logistics provided a raw flat dataset containing sales, vehicles, customers, branches, payments, deliveries, logistics costs, returns and customer-experience information. The challenge was not simply to build charts. The project required a complete journey from data-quality investigation to management decision support.

1. Understanding the Data

The dataset contains 276 records and 32 columns. The first step was to establish the dataset grain and examine potential identifiers, measures and descriptive attributes.Columns were named column 1 to column 32 each representing different data as shown below:
OrderID, Order date, Delivery date, customer name, age, type, Region, county, city, branch, sales rep, lead, car details, units sold, selling price, Discount, revenue recorded, delivery fee, logistic fee, customer rating, review count and returned.

2. Data Quality Investigation

The raw dataset contained multiple quality problems. These included duplicate Order IDs, mixed date formats, invalid dates, multiple currency formats, negative monetary values, invalid unit counts, discounts above 100%, invalid vehicle years, inconsistent categorical values and rating/review-count problems.

Rather than deleting unusual records automatically, the approach was to preserve the original information, create analytical fields and add quality flags.
Cleaned includes city names like Nkr changed to Nakuru, Msa to Mombasa, Eldo to Eldoret by replacing values Selecting column -> Right click -> Replace values -> Write the value to be replaced and value to replace with (Msa -> Mombasa )->OK
Then changed to proper case by Transform -> Capitalize each word ->OK.

3. Currency Standardization

Because management reporting must use KES, monetary fields were inspected for KES, USD, EUR and ZAR. Unmarked values were treated as KES in accordance with the assessment. Explicit foreign currencies were converted using a single documented exchange-rate set from the Central Bank of Kenya.

Reporting currency: KES. Unmarked monetary values are treated as KES as required by the assessment. Explicit USD, EUR and ZAR values are converted using one consistent rate set from the Central Bank of Kenya:

** USD 129.46 KES

EUR 148.46 KES

ZAR 7.96 KES
**

4. Power Query

Power Query was used to standardize dates, numeric fields, currencies, categories and quality flags while preserving the raw columns.

5. Data Model

The analytical design separates the central transaction fact from descriptive dimensions such as date, customer, vehicle, branch, sales representative and payment.
Proposed Model

Star-schema approach:

FactSales

DimDate

DimCustomer

DimVehicle

DimBranch

DimSalesRep

DimPayment

The final relationships should be one-to-many from dimensions to FactSales with a dedicated Date dimension.

6. DAX

Reusable DAX measures were created for revenue, units, orders, costs, gross profit, margin, logistics, returns, cancellations, ratings and time-based analysis. Core measures include Total Orders, Total Units Sold, Total Revenue, Total Cost, Gross Profit, Gross Profit Margin, Average Revenue per Unit, Logistics Cost, Logistics Cost %, Return Count, Cancellation Rate and Average Customer Rating.

7. Dashboard

The Executive Dashboard provides management with the high-level state of the business. Detailed pages then allow investigation of sales, profitability, vehicles, customers, branches, payments, logistics and exceptions.

8. Investigation and Insights

The final insights should be based on validated Power BI results rather than unsupported assumptions. Unusual transactions are investigated using documented business rules.
What the data said
Headline: KSh 408.6M revenue, 128 vehicles, KSh 7.8M gross profit, 0.02 gross margin.

9. Recommendations

Recommendations should connect directly to findings. The project avoids treating correlation as proof of causation and identifies areas requiring further investigation where the dataset cannot establish a reason.
*Challenges and Lessons *

1. PowerQuery issues with performance and ran slower.
2. Discovering that the delivery fee sits inside recorded revenue changed the profit definition. Testing a formula against a recorded column paid off more than any single cleaning step.
3. Recorded values are not automatically right. The recorded revenue column was less reliable than a recalculation from its own components. *Lesson: * understand the business meaning of each field before modelling. Most of the value came from questions asked before touching a visual.

Conclusion

The JCars project demonstrates the full BI workflow: raw data investigation, Power Query preparation, currency standardization, data modelling, DAX, interactive reporting, investigation and evidence-based management communication.