Introduction
For this project, I was given a vehicle sales dataset for JCARS Logistics and asked to turn it into useful business information using Power BI.
At first, the dataset looked like a normal sales table. However, after looking through the values more closely, I found that there were quite a few data-quality problems that could affect the analysis.
My goal was not just to create some charts. I first needed to understand the data, identify the problems, clean it carefully, build a model, create the required calculations, and then use the results to answer business questions.
The main tools I used were:
- Microsoft Power BI
- Power Query
- DAX
- Excel/CSV
1. Starting With the Raw Dataset
The original JCARS dataset contained 276 transaction records and 32 columns.
The columns included information about:
- Customers
- Vehicles
- Branches
- Sales representatives
- Locations
- Payments
- Deliveries
- Vehicle costs
- Discounts
- Revenue
- Customer ratings
- Returns
- Dates
The dataset was provided as a flat CSV file, so my first step was to bring it into Power BI and inspect the structure before making any changes.
I did not want to immediately start replacing values. I first wanted to understand what each column contained and what the problems were.
2. Investigating the Data
One of the first things I checked was the Order ID column because I needed to understand the grain of the data.
I treated each row as one sales order and used Order ID as the main identifier when checking for duplicates.
I also looked at the different categories and numerical fields to see whether the values were consistent.
This is where most of the data-quality problems started becoming visible.
For example, I found:
- Duplicate Order IDs
- Customer types written in different ways
- Invalid ages
- Inconsistent location names
- Vehicle spelling variations
- Invalid vehicle years
- Text values inside numerical columns
- Different payment and delivery status formats
- Mixed customer rating formats
- Multiple currencies
- Mixed date formats
- Negative and zero revenue values
Instead of treating every unusual value as an error, I tried to understand whether there was enough information to justify changing it.
That became an important rule throughout the cleaning process.
3. Cleaning the Data With Power Query
I used Power Query to work through the columns and standardize the dataset.
For example, customer types such as Company and Corporate were standardized to Business, while Individual and Person were standardized to Individual.
For vehicle information, I corrected spelling and formatting variations such as:
-
Corola→Corolla -
Harier→Harrier -
D Max→D-Max -
Lc200→LC200
I also cleaned fuel types, transmission values, payment methods, payment statuses, delivery statuses, customer ratings and units sold.
For example, the Units Sold column contained values such as:
3 cars
two
one
-1
NULL
N/A
I converted values such as 3 cars, two, and one into numerical values where their meaning was clear.
The negative -1 was treated as invalid rather than trying to interpret it as a legitimate quantity.
One thing I learned from this process was that cleaning is not simply about making everything look consistent. Each change needs a reason.
4. Handling Unusual Values
There were also values that I could identify as unusual but could not confidently correct.
For example, the Vehicle Year column contained 202A, which was clearly not a valid year, so I treated it as missing.
However, values such as 1899 and 2032 looked unusual but I did not have a reliable source to determine what the intended values should have been.
Instead of guessing, I retained them as anomalies.
I followed the same approach with some unusual financial values.
This helped me avoid making corrections simply because a value looked strange.
5. Currency Standardization
Currency was one of the more challenging parts of the project.
The monetary columns contained values using:
- KES
- KSh
- USD
- EUR
- ZAR
- Values without a currency indicator
- Some values containing
?
The project required the final financial analysis to use KES.
I therefore treated monetary values without an explicit currency as KES.
For identified foreign currencies, I converted the values to KES using one consistent set of exchange rates:
- 1 USD = 129.64 KES
- 1 EUR = 147.71 KES
- 1 ZAR = 7.98 KES
The rates were obtained from Google Finance and referenced on 27 September 2026 at 02:58 EAT.
After conversion, the main monetary fields were available as numerical KES values for analysis.
6. Cleaning the Dates
Dates were another area that required careful attention.
The Order Date and Delivery Date columns contained a mixture of:
- Normal dates
- Different text formats
- Excel serial numbers
- Missing values
- Invalid dates
Some examples included values such as 26-Mar-25, Excel serial values, and invalid dates such as April 31.
I handled the different formats in Power Query and converted the final columns to the Date data type.
Where a date could not be interpreted reliably, I treated it as missing instead of inventing a date.
I deliberately left the date columns until the later part of the cleaning process because they required more careful handling than some of the categorical columns.
7. Validating the Cleaned Dataset
After cleaning, I did not immediately move to visualization.
I first checked whether the changes had produced a usable dataset.
The final dataset contained:
- 255 records
- 34 columns
- 0 duplicate Order IDs
I also checked important data types such as:
- Order Date → Date
- Delivery Date → Date
- Units Sold → Whole Number
- Monetary fields → Numeric KES values
I also created an expected revenue calculation and compared it with the recorded revenue.
This helped identify rows where the recorded revenue did not match the expected calculation.
I did not automatically replace those values because a difference does not necessarily mean that the recorded value is wrong.
8. Building the Power BI Data Model
Once the data was cleaned and validated, I moved into modelling.
The cleaned JCARS table remained the main transaction table.
I also created a separate DimDate table for time-based analysis.
DimDate =
CALENDAR(
MIN(Jcars_data_working[Order Date]),
MAX(Jcars_data_working[Order Date])
)
I then added Year, Month Number and Month Name fields.
The relationship was:
DimDate[Date] one (1) to many (*) relationship Jcars_data_working[Order Date]
This kept the model simple while allowing me to analyse revenue and other measures over time.
9. Creating the DAX Measures
After creating the model, I created DAX measures for the main business calculations.
For example, Total Revenue was calculated as:
Total Revenue =
SUM(Jcars_data_working[Revenue Recorded (KES)])
For total cost, I used the project-defined cost structure:
Total Cost =
SUMX(
Jcars_data_working,
(Jcars_data_working[Unit Cost (KES)] *
Jcars_data_working[Units Sold])
+ Jcars_data_working[Delivery Fee (KES)]
+ Jcars_data_working[Logistics Cost (KES)]
)
Gross Profit was then:
Gross Profit =
[Total Revenue] - [Total Cost]
And Gross Profit Margin was:
Gross Profit Margin =
DIVIDE(
[Gross Profit],
[Total Revenue]
)
I also created measures for orders, units sold, average selling price, returns and average delivery days.
The purpose of using DAX measures was to make the calculations reusable across different visuals and filters.
[Insert Screenshot: DAX measures in Power BI]
10. Building the Dashboard
After the data preparation and modelling were complete, I started building the management dashboard.
I created a one-page dashboard titled:
JCARS SALES PERFORMANCE OVERVIEW
I wanted the dashboard to answer the main business questions without making it too crowded.
The dashboard contains five KPI cards:
- Total Revenue
- Gross Profit
- Gross Profit Margin
- Total Units Sold
- Total Orders
I also created visuals showing:
- Revenue over time
- Revenue by branch
- Revenue by vehicle type
- Gross profit by vehicle type
- Gross profit by branch
- Average selling price vs. unit cost by vehicle type
I added two slicers:
- Vehicle Type
- Branch
These allow the user to interact with the dashboard and investigate different parts of the business.
11. What the Dashboard Showed
The final dashboard showed approximately:
- KSh1.24 billion revenue
- 415 units sold
- 255 orders
- -KSh103.27 million gross profit
- -8.34% gross profit margin
The profitability result was one of the most important parts of the analysis.
When I looked at the cost structure more closely, I found that vehicle costs were already higher than recorded revenue before delivery and logistics costs were added.
Delivery and logistics costs increased the overall loss further.
This helped me understand why looking at revenue alone would not provide a complete picture of business performance.
12. Vehicle and Branch Analysis
The dashboard also showed differences between vehicle types.
Some vehicle types generated positive gross profit while others generated negative gross profit.
The same pattern appeared across branches, where profitability varied between locations.
This does not automatically mean that a particular vehicle type or branch is performing badly. The results provide areas for further investigation.
For example, management could investigate differences in:
- Vehicle acquisition costs
- Selling prices
- Discounts
- Delivery costs
- Logistics costs
- Sales volume
This is one reason I included the comparison between average selling price and unit cost.
13. Business Questions
The analysis was built around questions such as:
- What are the total revenue, orders and units sold?
- What is the overall gross profit?
- How does revenue change over time?
- How does revenue differ between branches?
- How does revenue differ between vehicle types?
- Which vehicle types generate positive or negative gross profit?
- Which branches generate positive or negative gross profit?
- How do selling prices compare with unit costs?
- How do delivery and logistics costs contribute to overall cost?
These questions helped me decide which calculations and visuals were actually useful.
14. Management Insights
From the analysis, I identified several important patterns.
First, JCARS generated approximately KSh1.24 billion in recorded revenue across 255 orders.
However, the overall gross profit was approximately -KSh103.27 million.
The analysis also showed that vehicle costs were already higher than recorded revenue before delivery and logistics costs were included.
Profitability also varied between vehicle types and branches.
Another useful observation was that revenue changed over time rather than remaining constant throughout the period.
The selling price versus unit cost comparison also helped show that some vehicle types had a relatively small difference between their selling prices and costs.
15. Recommendations
Based on the patterns identified in the dashboard, I would recommend that management:
- Review vehicle pricing and costs, especially for vehicle types showing negative gross profit.
- Investigate branches with negative profitability to understand differences in costs, sales and revenue.
- Review delivery and logistics costs and identify areas where these costs can be controlled without affecting service quality.
- Monitor vehicle profitability regularly instead of focusing only on revenue and units sold.
These recommendations would need further investigation before major business decisions are made.
16. Challenges I Faced
One of the biggest challenges was the amount of inconsistency in the raw dataset.
Some values were simple to fix, while others required more thought.
Currency conversion was particularly challenging because the dataset contained multiple currencies and some values did not have a currency indicator.
Dates were also difficult because the same column could contain different formats and invalid values.
Another challenge was deciding what to do with unusual values.
I learned that cleaning data does not mean changing every value that looks unusual. Sometimes the most appropriate decision is to leave a value as an anomaly when there is not enough evidence to correct it.
17. What I Learned
This project helped me understand the importance of data quality before analysis.
Before starting the project, it was easy to think that the main part of Power BI was creating charts and dashboards.
After working through the dataset, I realised that much of the important work happens before the dashboard is created.
I learned how to:
- Investigate a raw dataset
- Identify data-quality problems
- Clean data using Power Query
- Standardise categories
- Handle missing and invalid values
- Convert currencies
- Work with difficult date formats
- Validate cleaned data
- Build a simple Power BI model
- Create DAX measures
- Design a management dashboard
- Use visualisations to answer business questions
Most importantly, I learned that the decisions made during data cleaning can directly affect the results shown in the dashboard.
18. Conclusion
This project took the JCARS Logistics dataset from a raw and inconsistent CSV file to a cleaned dataset, Power BI model, DAX calculations and an interactive management dashboard.
The process was not just about creating visuals. I first had to understand the data, identify the problems, decide how each issue should be handled, validate the results and then build the analysis.
The final dashboard showed that JCARS generated significant revenue but had negative gross profit based on the cost definition used in this project.
The differences between vehicle types and branches also provided areas that management could investigate further.
Overall, this project gave me practical experience with Power Query, data cleaning, data modelling, DAX, Power BI visualisation and business analysis.
It also showed me that a good dashboard starts with good data preparation and clear business questions.
Tools Used
- Microsoft Power BI
- Power Query
- DAX
Project Files
The complete project repository contains the Power BI file, datasets, report documentation and dashboard screenshots.
