From Messy CSV to Boardroom-Ready: Cleaning, Modelling and Visualizing JCars Logistics' Sales Data in Power BI

1. Project Background and Business Objective

JCars Logistics imports, sells and delivers vehicles to customers across Kenya. Management requested a Power BI solution to understand the company's overall performance and identify areas that may need attention. The analysis covers sales, revenue, costs and profitability, vehicle performance, branch and regional performance, sales representatives, lead sources, payments, deliveries and logistics, returns and cancellations, customer experience and unusual transactions that may need further investigation.

This report explains the journey from the raw dataset to the final Power BI solution. It covers data cleaning, data modelling, DAX measures, dashboard development and the main insights and recommendations from the analysis.

2. Dataset and Grain

The source data was a single raw flat file containing 32 columns and 276 rows. Each row represents one vehicle sold in one sales transaction. This level of detail was kept throughout the project. No aggregation or splitting was needed when creating the fact table.

The columns were grouped into several main areas:

• Transaction information such as Order ID and dates
• Customer information
• Vehicle information
• Location information such as Region, County, City and Branch
• Sales information such as Sales Rep and Lead Source
• Financial information such as prices, costs, discounts, fees and revenue
• Operational information such as payment, delivery, returns and customer ratings

Screenshot: Raw dataset showing the original columns and sample records before cleaning.

3. Data Quality Audit: Key Issues Identified

More than ten major data quality issues were identified and resolved during the cleaning process. The most important ones are summarised below. The same cleaning approach was applied consistently across the dataset.

4. Currency Standardization

All monetary values were converted to Kenya Shillings (KES) so that the report could use one consistent reporting currency. This also followed the assessment instruction that values without a currency label should be treated as KES.

Exchange rates used:

The ? symbol that appeared before some monetary values was investigated by comparing the affected Unit Selling Price with the Unit Cost in the same row. The values consistently matched USD-level pricing, so the ? symbol was treated as a corrupted USD symbol rather than making an unsupported assumption.

Zero-value business rules:

• Unit Selling Price / Unit Cost: zero was treated as missing because a vehicle would not normally have zero acquisition or selling value.
• Delivery Fee: zero was accepted because free delivery is possible.
• Logistics Cost: zero was treated as missing where DeliveryStatus showed that the vehicle had actually been delivered.
• Revenue Recorded: zero was accepted where PaymentStatus was Cancelled or Refunded. In other cases it was treated as missing.

This was the formula format I used to work on the monetary columns with different currencies:

fnMoneyKES = (v as any) as nullable number =>
    let
        raw = Text.Trim(Text.From(v) ?? ""),
        upperRaw = Text.Upper(raw),
        isMissing = raw = "#VALUE!" or raw = "#ERROR" or raw = "-" or raw = ""
                    or upperRaw = "NULL" or upperRaw = "TBD"
                    or Text.Contains(upperRaw, "MISSING") or Text.Contains(upperRaw, "NOT"),
        hasM = Text.EndsWith(upperRaw, "M"),
        currency =
            if Text.Contains(upperRaw, "KES") or Text.Contains(upperRaw, "KSH") then "KES"
            else if Text.Contains(raw, "$") or Text.Contains(upperRaw, "USD") then "USD"
            else if Text.Contains(upperRaw, "EUR") then "EUR"
            else if Text.Contains(upperRaw, "ZAR") or Text.StartsWith(Text.Trim(raw), "R ") then "ZAR"
            else if Text.Contains(raw, "?") then "USD"
            else "KES",
        numText = Text.Select(raw, {"0".."9", "."}),
        numVal = try Number.From(numText) otherwise null
    in
        if isMissing or numVal = null then null
        else if hasM then Number.Abs(numVal) * 1000000
        else if currency = "USD" then Number.Abs(numVal) * 129.54
        else if currency = "EUR" then Number.Abs(numVal) * 147.84
        else if currency = "ZAR" then Number.Abs(numVal) * 7.93
        else Number.Abs(numVal),

5. Data Validation Performed

Several validation checks were carried out after the main cleaning process:

• Cross-checked currency assumptions against Unit Cost and Unit Selling Price values in the same row before finalizing the conversion logic.
• Compared Payment Status and Delivery Status for logical consistency. This identified 14 records where the two fields did not agree. There were 10 records showing "Paid but Delivery Cancelled" and 4 showing "Payment Cancelled but Delivered". These records were kept as they were and flagged for investigation rather than being changed without evidence.
• Checked the two blank Vehicle Type records against their Car Model. Each model had a consistent Vehicle Type in the other records, allowing the missing values to be inferred.
• Checked Branch and City relationships before creating the location hierarchy. Each branch consistently belonged to one city.
• Checked Sales Rep and Branch relationships. Sales representatives worked across multiple branches, which supported creating Sales Rep as a separate dimension.

6. Data Model

I transformed the raw flat file into a star schema consisting of one fact table and eight dimension tables.

Grain of FactSales: one row represents one vehicle sold in one transaction. This is the same grain as the original dataset.

FactSales (Fact Table)

Dimension Tables

Relationships: all dimensions relate to FactSales as One-to-Many with single-direction cross-filtering. FactSales relates to DimDate twice, using OrderDate as the active relationship and DeliveryDate as the inactive relationship. The DeliveryDate relationship is activated in specific measures using USERELATIONSHIP.

Model View showing FactSales at the centre and the eight dimension tables connected through One-to-Many relationships, including the two DimDate relationships for Order Date and Delivery Date.

Modelling limitation: the dataset does not contain a unique customer identifier. DimCustomer was therefore built from distinct combinations of CustomerName, CustomerType and CustomerAge.I did this by selecting all the columns in it and removing duplicates.

This means that "customer" in this analysis refers to the customer as recorded per transaction rather than a verified master customer record. Two different people with the same name and similar profile be treated as one customer.

I considered this limitation when presenting customer-level findings.

7. Key DAX Measures

The following are some of the main DAX measures I used in the analysis.

Total Revenue = SUM(FactSales[RevenueRecorded])

Total Gross Profit =
SUMX(FactSales, FactSales[RevenueRecorded] - (FactSales[UnitsSold] * FactSales[UnitCost]))

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

Return Rate % =
DIVIDE(
    CALCULATE(COUNTROWS(FactSales), FactSales[Returned] = "Yes"),
    COUNTROWS(FactSales)
)

Logistics Cost % of Revenue = DIVIDE([Total Logistics Cost], [Total Revenue])

Customer Total Revenue =
CALCULATE([Total Revenue], ALLEXCEPT(FactSales, DimCustomer[CustomerName]))

Examples of the measures I created for the analysis by use of DAX.

Business definition: Gross Profit was defined as Revenue minus (Units Sold times Unit Cost).I excluded Delivery Fee and Logistics Cost because I treated them as operating expenses rather than the direct cost of the vehicle.

Limitation: some Unit Cost and Unit Selling Price values could not be recovered during cleaning and were changed to null. Therefore, Gross Profit and Gross Profit Margin may be affected for transactions where cost or price information was missing.

8. Dashboard and Report Structure

Interactivity was also added to the report. Region and Car Make slicers are synced across pages. A Region → County → City → Branch drill-down was created within the matrix visual and visuals on each page can interact through cross-filtering.

The Executive Dashboard showing KPI cards, revenue trend, branch and make comparison, payment status mix and logistics cost attention indicator.

How the dashboard looks like when the slicer only applies to Central region.

9. Investigation: Records Requiring Management Attention

1. 14 Payment/Delivery Status inconsistencies: 10 records show Payment Status = Paid while Delivery Status = Cancelled. Another 4 show Payment Status = Cancelled while Delivery Status = Delivered. These records were not corrected because the data does not show which field is inaccurate. They were instead flagged for manual review.
2. Truck vehicle type shows a -66% gross profit margin: this is the vehicle type with the most negative margin in the dataset. It should be investigated to determine whether the result comes from a genuine pricing or cost issue or from remaining data quality problems.

1. BMW (-37%) and Isuzu (-31%) show negative margins: both makes show significant negative margins even though their selling prices are comparable with other makes. The Unit Cost data for these makes should therefore be reviewed to check whether currency conversion or missing-value handling affected the results.

1. Nairobi has the highest average delivery time: Nairobi has an average delivery time of 26.29 days compared with the company-wide average of 15.18 days. Since Nairobi hosts the company's headquarters, this difference is worth investigating to understand what may be causing the longer delivery times.

10. Management Insights

1. Revenue is concentrated in a small number of vehicle makes and regions. Toyota accounts for the largest share of revenue among the vehicle makes in the dataset. Rift Valley (KSh 381.9M) and Central (KSh 244.6M) also contribute a large share of the total KSh 1.48bn revenue. This means overall performance is strongly connected to demand for Toyota vehicles and performance in these regions.

1. Customer revenue is concentrated but not extreme.
   The top 10 customers out of 276 account for approximately KSh 299.3M, which is around 20% of total revenue. This shows that a meaningful share of revenue comes from a relatively small group of high-value customers.

2. Some vehicle categories are being sold at negative margins.
   SUVs generated the highest revenue among vehicle types at approximately KSh 845M and had a positive margin of 8%. However, Sedans had a -9% margin, Crossovers -6%, Vans -28% and Trucks -66%. Since these are categories with meaningful sales volumes, the results may point to wider pricing or cost issues rather than a few individual transactions. However, these findings should be considered alongside the data quality issues identified during cleaning.

3. Customer ratings do not show a clear relationship with transaction value.
   The scatter plot comparing Customer Rating and Revenue Recorded shows that ratings generally cluster around 3 to 4 across different transaction values. There is no clear upward or downward pattern in the data. Based on this dataset, higher transaction values do not appear to be directly associated with higher customer ratings.

1. Logistics efficiency varies across branches. Nairobi HQ has the highest Logistics Cost % of Revenue among the branches. Nairobi also has the longest average delivery time at 26.29 days compared with the overall average of 15.18 days. These two findings together suggest that Nairobi's logistics performance deserves further investigation.

Note: Return Rate (31.88%) and Gross Profit Margin (2.53%) were calculated from the cleaned dataset. Both figures should be treated carefully because missing or null values in Unit Cost, Unit Selling Price and Returned may affect the results. Additional source data could therefore change these figures.

11. Management Recommendations

1. Review pricing and costs for Trucks, Vans, Sedans and Crossovers. These vehicle categories show negative gross margins at meaningful sales volumes. Management should review their selling prices and acquisition costs to determine whether current pricing is covering the cost of the vehicles.

2. Review the 10 Paid but Cancelled transactions. The 10 records showing Payment Status = Paid and Delivery Status = Cancelled should be reviewed to determine whether customers are owed refunds or whether the delivery status is incorrect.

3. Investigate Nairobi's delivery times and logistics costs. Nairobi has both a relatively high logistics cost share and the longest average delivery time. Management should investigate the reasons behind this before treating it as normal regional variation. Possible areas to review include traffic, yard-to-customer handoffs, delivery scheduling and available resources.

12. Analyst-Defined Business Questions

In addition to the questions provided in the assessment brief, five additional questions were identified as useful for JCars Logistics management. Three were answered directly by the Power BI analysis.

1. Does higher customer satisfaction translate into higher-value sales, or are they unrelated? (Answered: Section 10, Insight 4)

1. Which lead source delivers the best return per lead, and is marketing investment allocated accordingly? (Answered via the Avg Revenue per Lead by Lead Source visual)

1. Are certain branches carrying disproportionate logistics costs relative to the revenue they generate? (Answered: Section 10, Insight 5)

1. Does vehicle age affect profitability, suggesting that older stock should be discounted or phased out?

2. How concentrated is revenue among the top customers, and does this represent a meaningful business risk? (Answered: Section 10, Insight 2)

13. Assumptions and Business Rules Summary

• Monetary values without a stated currency were treated as Kenya Shillings based on the assessment instructions.
• Exchange rates used were USD 129.54, EUR 147.84 and ZAR 7.93 to KES.
• Gross Profit = Revenue minus (Units times Unit Cost). Delivery Fee and Logistics Cost were treated as separate operating expenses.
• "County Government" was standardized under "Government" because the dataset did not provide enough information to distinguish between different levels of government.
• "Retail" was kept as its own Customer Type category rather than being combined with "Individual."
• Branch and Yard designations for the same city were kept as separate locations.
• Ambiguous dates were interpreted using the day-first UK/Kenya convention.
• Ratings outside 0 to 5 and discounts below 0% or above 50% were treated as unreliable and changed to null rather than being manually corrected.
• DimCustomer was created using Name + Type + Age because the source data did not contain a unique customer identifier. Customer-level findings should therefore be interpreted with this limitation in mind.

14. Challenges Encountered and Learning

The biggest challenge in this project was the number and variety of data quality issues spread across the dataset.

Many cleaning decisions could not be made by simply applying the same rule to every column. Instead, related fields had to be compared to understand what the data was actually showing.

For example, Unit Cost and Unit Selling Price were compared when resolving unclear currencies. Car Model was compared with Vehicle Type when filling missing Vehicle Type values. Payment Status was also compared with Delivery Status to identify records that needed further investigation.

For me this project solidified the importance of making data cleaning decisions based on evidence wherever possible. When the available information was not enough to confidently correct a value, the value was left as null or flagged for investigation instead of making an unsupported assumption.

The process also showed that data cleaning is not just about making a dataset look neat. Every cleaning decision can affect the final analysis and the business conclusions drawn from it. Clearly documenting these decisions is therefore an important part of producing a reliable Power BI solution.

Github Repository Link