Introduction
The JCars vehicle sales dataset contains information about customers, vehicles, sales transactions, locations, payments, deliveries and revenue. However, the data contains inconsistencies such as different formats, missing values and incorrectly entered information.
The goal of this project is to clean and standardise the dataset, organise it into fact and dimension tables, create the necessary relationships and prepare it for analysis in Power BI. This will make the data easier to understand and help us generate useful insights from JCars’ sales performance.
1. Understanding the Dataset
I started by reviewing the JCars vehicle sales dataset to understand its structure, columns and the type of information available. The dataset contained sales, customer, vehicle, location, payment, delivery and revenue information.
Raw Jcars Dataset
2. Cleaning the Data
The next step was to clean the dataset in Power Query. This involved removing unnecessary spaces, correcting spelling inconsistencies, standardising categories and handling missing or invalid values.
I cleaned fields such as Customer Type, Region, County, Lead Source, Car Make, Vehicle Type, Fuel Type, Transmission, Payment Method,and Delivery Status.
For numerical fields such as Units Sold, Selling Price, Cost, Delivery Fee, and Review Count, I converted the values into the correct numerical data types. Currency symbols and commas were removed where necessary.
Date columns such as Order Date and Delivery Date were also converted into proper date formats, while invalid dates were handled as errors/null values.
Applied steps on Jcars dataset
3. Standardising and Validating the Data
I also standardised values that appeared in different formats. For example, different variations of customer types, regions, vehicle types and payment methods were grouped into consistent categories.
The revenue information was also checked against the expected revenue calculation to identify inconsistencies in the dataset.
Cleaned dataset
4. Creating Fact and Dimension Tables
After cleaning the data, I structured it into a star schema to make the data easier to analyse in Power BI.
The main Fact Table contains transactionlevel sales information, while the dimension tables provide descriptive information about different parts of the business.
The model includes tables such as:
- Fact_table-sales transactions and measures
- Dim_customer-customer information
- Dim_Vehicles-vehicle information
- Dim_date-dates related analysis
- Dim_salesrep-sales representative information
-
Dim_location-location related information like branches,region,county and city.
Fact and dimension tables in a Star schema
5. Creating Relationships
The next step was creating relationships between the fact table and dimension tables. The dimensions use unique IDs, while the fact table contains the corresponding foreign keys.
For example,Customer ID in Dim_customer connects to Customer ID in Fact_table.
For location data, if the fact table does not have a Location ID, it can be created by merging the fact table with the location/branch dimension using matching fields such as ``Region, County, City,and Branch, then expanding the Location ID into the fact table.
The relationships are generally one-to-many (1:*), with the dimension table on the one side and the fact table on the many side.
Model showing relationshhips
6. Preparing for the Dashboard
After cleaning,transforming,validating and modelling the data, I created an interactive Power BI dashboard to present the main JCars sales information clearly.
The dashboard includes key KPIs such as Total Revenue, Total Cars Sold, Total Sales, Gross Profit and Gross Margin. I also added slicers for Customer Type, Order Date, Region, Sales Representative and Vehicle Typeto make it easier to explore the data.
The visuals show performance by sales representative, vehicle type, customer type, and region, helping to highlight differences in sales performance across the business.
Final JCars dashboard
Conclusion
This project took the JCars dataset from raw and inconsistent data to a structured and interactive Power BI dashboard. I worked through data cleaning, standardisation, validation, modelling, relationships and DAX calculations before presenting the results visually.
The project helped me understand that creating a good dashboard is not just about making charts.The quality of the insights depends on how well the data is prepared, modelled and analysed. Overall, it gave me practical experience in turning raw data into useful information that can support business analysis and decision making.
Top comments (0)