From Messy Vehicle Records to a Power BI View of JCars Logistics

Introduction

A dashboard can make information easier to explore, but it cannot make unreliable information reliable by itself. For our JCars Logistics Power BI project, we wanted to bring vehicle sales, customer, payment, delivery, return, and cost information into one place and make it easier to spot questions management should investigate.

The source was a CSV with 276 rows and 32 columns. It covered orders from 2025 and 2026, with fields for customers, vehicles, branches, sales representatives, prices, discounts, delivery, and recorded revenue. It looked like a straightforward table at first. A closer look showed why we needed to spend time understanding the data before trusting the visuals.

Data Exploration

The first challenge was variety. The source stored its fields as text, even when a field was meant to be a date, a number, a percentage, or money. Dates appeared in several formats, including text dates and spreadsheet serial numbers. Some were impossible dates or error labels. Prices and costs included currency symbols, separators, and shorthand such as M; some values were negative or missing. Categories also varied in case and spelling, and payment, delivery, and return fields used several versions of similar labels.

Identifiers needed attention too. Nineteen order IDs were blank or placeholders, and three usable IDs were repeated. We found no exact duplicate rows, so dropping duplicates would not have solved the key problem. It would also have risked removing legitimate records. We treated the rows as transaction records, while keeping in mind that the data did not prove that every row represented a completed sale.

That profiling changed the question from “How do we make every column look tidy?” to “Which values can we interpret with confidence, and how should we show the ones we cannot?”

Data Cleaning

For unit selling price, we used Power Query to create a numeric value that Power BI could aggregate. We kept the original value in a companion column, Unit Selling Price Original, so that a converted amount could still be checked against what came from the CSV.

The transformation trims and reads the source text, recognises common missing or error markers, extracts the numeric portion, handles thousands separators and a trailing M, identifies currency markers, converts the amount to Kenyan shillings, and rounds it to two decimal places. Blank, unparseable, and negative selling prices become null rather than zero. The project brief called for unmarked monetary values to be treated as KES. The query also interprets a ? currency marker as USD, based on the pattern noted during the project.

The cleaned export preserves all 276 source rows and adds the original-price field. It has 12 selling-price values that remain null after applying the parsing rules. Keeping those rows lets us continue analysing the fields that are available without inventing a price for them.

Other data issues we had to account for

The price conversion was one part of the work; the audit also surfaced issues that affect the rest of the report:

• Dates: 20 order dates and 28 delivery dates did not parse under tolerant checks.
• Categories and statuses: branch names, vehicle makes, lead sources, and operational labels have spelling and case variants.
• Discounts: values appear as percentages, decimals, and text.
• Money: costs, fees, logistics amounts, and recorded revenue include placeholders, negative numbers, and mixed formats.
• Revenue reconciliation: we compared recorded revenue with a separate check calculated as units sold × converted unit selling price × (1 − discount rate). This was a comparison formula, not a replacement for the source value: delivery fees, returns, cancellations, and other business rules may affect the final amount. Of 205 rows where both sides could be compared, only 30 were within 1%. That is a strong reason to investigate the row-level differences before treating revenue, profit, or margin as settled facts.

Data Modelling

The report brings the transaction table together with lookup tables for customers, branches, and sales representatives. In the model view, these connect to Jcars_data through one-to-many relationships, so a selected customer, branch, or representative can filter the related transaction records.

We used explicit measures for headline values such as recorded revenue, units sold, gross profit, margin, average revenue per unit, unpaid revenue, logistics cost, customer rating, returns, and delivery turnaround. The report then lets users explore those measures through filters for dimensions such as region, vehicle type, and month.

The screenshots show seven report pages: an Executive Overview, Sales and Vehicle Performance, Profitability and Discounts, Customers and Commercial Performance, Payments and Logistics, Returns and Customer Experience, and Recommendations. Each page groups related questions rather than trying to fit every chart onto one canvas.

What the pages help us ask

The overview puts the main indicators together and pairs them with branch and monthly revenue views. The sales page compares makes, vehicle types, and monthly activity. The profitability page puts revenue, vehicle cost, gross profit, margin, and discount bands side by side. Customer and commercial views compare customer types, lead sources, and sales representatives. The payments and logistics page surfaces paid and unpaid revenue alongside delivery and logistics measures. The returns page brings return indicators, cancellations, ratings, and flagged transactions into view.

The “Needs Attention” area on the overview is useful because it brings unpaid revenue, returns, cancellations, and delayed deliveries out of the background. The exceptions table on the returns page serves a similar purpose at a more detailed level: it gives a reviewer records to follow up rather than hiding them inside a single total.

Data Insights

The report snapshot displays about KES 1.35 billion in recorded revenue and 452 units sold. It also shows negative gross profit and gross margin. Those numbers are useful prompts for investigation, but they should not be presented as a definitive statement of business performance while recorded revenue, converted costs, discounts, and transaction outcomes still need reconciliation.

That caveat is part of the analysis, not a footnote to it. A report is more useful when it shows where the data raises questions as well as where it gives a clear answer. For example, a high return count or a branch with a long average delivery turnaround can guide a follow-up review. The report alone cannot explain why a return happened or prove that a channel generated a profitable sale.

What we learned

The project reinforced a few practical lessons for us:

1. Keep the source visible. Retaining the original selling-price text made it easier to audit the converted number.
2. Treat missing as missing. Replacing blanks or parse errors with zero can distort totals and averages.
3. Do not confuse a tidy chart with a validated measure. Recorded and derived revenue need to agree, or their differences need an explanation.
4. Standardisation needs rules. A clear spelling variant can be mapped; an ambiguous status should not be guessed into a convenient category.
5. Make room for investigation. Summary cards show where attention may be needed, while detail tables help people find records to review.