From Messy Transactions to Business Insights: My JCars Power BI Project

When I started working on the JCars dataset, my first instinct was to think about the dashboard.

Which KPIs should I create?
Which visualizations should I use?
How should the report look?

But I quickly realised that the dashboard was not the difficult part.

The difficult part was understanding whether the data underneath it could actually be trusted.

That changed how I approached the entire project.

The Dataset

The JCars dataset contained 276 transaction records and 46 columns, covering areas such as customers, vehicles, locations, sales, payments, delivery and costs.

My goal was to turn the raw transactional data into a Power BI report that could help answer questions around:

• Sales performance
• Revenue and profitability
• Vehicle performance
• Customer and sales channels
• Delivery performance
• Operational costs
• Returns and cancellations

But before answering those questions, I needed to understand what each row actually represented.

Understanding the grain

I established that one row represented one transaction/order record.

That sounds straightforward, but it became important later when I started investigating duplicate Order IDs.

The First Problem: Order IDs Were Not Always Unique

One of the first things I noticed was that some Order IDs appeared more than once.

The duplicated IDs included:

• ord1020
• CAR1086
• ord1174

At first, deleting duplicates might seem like the obvious solution.

But I decided to investigate them instead.

The duplicate records contained differences in other attributes such as customer information, location and customer type.

That raised an important question:

Were these actually duplicate records, or were they separate transaction records sharing the same source Order ID?

Without a separate transaction identifier, I could not confidently assume that the records were duplicates.

So instead of deleting them, I retained the records and created a unique Transaction Key for each fact-row.

The original Order ID was retained as a business/source reference.

This was one of the biggest lessons from the project:

A duplicate value is not automatically a duplicate record.

Sometimes the right response to a data-quality problem is not to delete data, but to investigate what the data is actually telling you.

Investigating Before Cleaning

I used Excel extensively during the investigation stage.

This allowed me to look at the dataset column by column and identify patterns before transforming anything.

I found inconsistencies across several fields, including:

• Customer Type
• Region
• County
• City
• Branch
• Lead Source
• Car Make
• Fuel Type
• Transmission
• Vehicle Year
• Discount
• Prices and costs
• Delivery dates
• Delivery status

There were also values such as blanks, N/A, NULL, inconsistent capitalisation, spelling variations and values that required further investigation.

For example, vehicle makes contained variations such as Toyota, TOYOTA and other misspelled variants.

The important distinction for me was between standardising a genuine variation and assuming two different values represented the same real-world entity.

That distinction became particularly important for fields such as sales representatives, where names like Faith and Faith Achieng appeared.

I did not automatically merge these because the dataset did not provide enough evidence to prove they represented the same person.

Cleaning and Transformation

After investigating the raw data, I moved into Power Query.

The goal was not simply to make the dataset look cleaner.

The goal was to make the data more consistent, usable and analytically reliable while preserving the underlying transaction records.

The cleaning process included:

• Standardising categorical values
• Correcting inconsistent text formats
• Handling missing and placeholder values
• Converting columns to appropriate data types
• Validating numerical fields
• Investigating date anomalies
• Creating calculated fields required for analysis
• Checking the results after transformation

After the cleaning process, the final fact table retained all 276 records, with zero technical Power Query errors.

Dates Needed Special Attention

Dates were one of the more complicated areas of the dataset.

The source contained a mixture of valid dates, blanks, placeholder values, Excel serial numbers and invalid dates.

I also found delivery records where the calculated delivery period was negative.

Four examples were:

• LCL-1080
• CAR1219
• ord1229
• LCL1236

Instead of silently removing these records, I treated them as data-quality considerations that needed to remain visible during analysis.

That approach matters because cleaning data does not mean pretending that problematic records never existed.

Building the Data Model

Once the data was prepared, I moved from cleaning into modelling.

Rather than keeping everything in one large table, I created a star-schema structure around a central Fact Sales table.

The model included dimensions for:

• Customer
• Vehicle
• Location
• Sales
• Payment
• Delivery
• Date

The fact table contained the transactional measures and keys needed for analysis.

I also created a dedicated Date table covering 1 January 2025 to 15 July 2026.

The Order Date relationship was active, while Delivery Date was kept inactive so that delivery-related analysis could use it when specifically required.

This was another point where the project moved beyond simply creating visuals.

The model structure determines how reliably those visuals answer questions.

Adding Business Measures with DAX

With the model in place, I created measures for the main business questions.

Some of the measures included:

• Total Revenue
• Total Units Sold
• Total Cost
• Total Profit
• Profit Margin
• Average Delivery Days
• Average Discount
• Total Delivery Fees
• Total Logistics Cost
• Revenue Per Unit
• Total Transactions
• Average Units per Transaction
• Profit per Transaction

One example was Total Cost, calculated from units sold and unit cost rather than simply summing a pre-existing total.

This allowed the report to analyse profitability at a more meaningful level.

Turning the Model Into a Report

The final Power BI report was organised into three pages.

1. Executive Overview

This page focused on the overall performance of the business.

The main KPIs included:

• $1.273B Total Revenue
• $455.46M Total Profit
• 36% Profit Margin
• 452 Units Sold
• 276 Transactions
• 16.76 Average Delivery Days

The purpose was to give a decision-maker a quick view of the overall situation before going deeper.

2. Sales & Profitability

This page explored sales volume, revenue and profitability across dimensions such as vehicle make, location and lead source.

One example from the analysis was Toyota, which generated approximately $540.8M in revenue from 137 units.

At the same time, not every high-revenue category performed equally from a profitability perspective.

For example, BMW generated approximately $50.9M in revenue but had a negative margin of around 2% in this dataset.

That creates a business question rather than simply being a number on a chart:

What is driving the negative profitability?

Potential areas for further investigation would include acquisition cost, selling price, discounts and the underlying transaction records.

3. Delivery & Operations

The third page focused on delivery performance and operational costs.

The dataset contained several delivery statuses, including:

• Delivered
• In Transit
• Cancelled
• At Yard
• Delayed
• Held

The report allowed delivery performance and delivery-related costs to be examined alongside those statuses.

For example, records marked as Held had an average delivery period of approximately 25 days, compared with approximately 15.4 days for Delivered records.

Again, the dashboard does not automatically explain why this happens.

It provides a starting point for asking the next business question.

What I Learned

The biggest lesson from this project was that Power BI is not just about building dashboards.

The dashboard is the visible part.

A large part of the actual analytical work happens before the first visual is created.

I learned to ask questions such as:

What does one row represent?

Can I trust this identifier to be unique?

Is this really a duplicate, or just a repeated value?

Should these two values actually be merged?

What does this missing value mean?

Is this date valid?

Does this relationship make business sense?

What assumptions am I making?

Those questions changed the way I approached the project.

Final Takeaway

I went from looking at a 46-column dataset and asking “What visual should I build?” to asking:

“What does this data actually allow me to conclude?”

That shift has probably been the most valuable part of the project.

Power BI helped me turn the data into something visual and interactive.

But the real work was learning how to question the data before trusting the numbers.

And that is the part of data analytics I want to keep developing.

Github: [(https://github.com/majalealex-ux/JCars-Sales-Performance-Analysis/tree/main)]