Introduction
Building a dashboard is only one part of a successful Business Intelligence (BI) project. Before a visual can provide meaningful insights or support a business decision, the underlying data must first be understood, cleaned, standardised, modelled, and validated. This was the central lesson from my JCars Logistics Power BI project, where I developed an end-to-end BI and data analytics solution for a vehicle sales and logistics business.
The project involved transforming a raw transactional dataset into an interactive, executive-ready Power BI performance and diagnostic platform. The source data contained information relating to vehicle sales, customers, sales representatives, branches, payment methods, deliveries, logistics costs, returns, customer ratings, and revenue. The primary objective was to analyse key areas of the business, including sales performance, profitability, operational efficiency, delivery logistics, and customer satisfaction, while turning the underlying data into actionable insights for management.
However, the dataset reflected many of the challenges commonly encountered when working with real-world business data. It contained duplicate identifiers, inconsistent date formats, invalid calendar dates, inconsistent categorical values, text within numeric fields, multiple currencies, and financial figures that did not always reconcile.
This article walks through the process I followed to transform this unrefined CSV dataset into a structured, validated Power BI data model and ultimately an interactive management dashboard capable of supporting evidence-based business decisions.
Data Preprocessing & Cleansing
To prepare the dataset for analysis, text columns underwent systematic cleaning. I trimmed extra whitespace, capitalized each first letter and converted placeholder strings such as N/A, null, unknown, and blank entries into standardized null values. Using targeted find-and-replace rules, I corrected frequent typing errors and abbreviation inconsistencies across entities like branches, sales reps, lead sources, and vehicle attributes e.g., standardizing Toyta to Toyota.
Categorical features were consolidated to ensure uniform grouping; for instance, terms like person, retail, and individual were normalized to Individual. Finally, missing location data was imputed hierarchically using existing domain knowledge: missing counties were populated based on branch locations, and missing regions were inferred directly from county names. Numbers were removed from names of individuals e.g A1sha to Aisha.
Understanding the KPIs
What is the total number of cars sold? What is the total sales revenue generated by the company?
What is the total gross profit and gross profit margin?
Which car makes generate the strongest business performance?
Which car models perform strongly based on units sold?
Data Modelling
I structured the analytical model as a Star Schema centered on Fact_Sales, linking dimensions with integer surrogate keys. To support time-series reporting across multiple milestones such as order and delivery dates I implemented the tables icluding fact_sales, dim_loation, dim_sales, dim_customer
, dim_location.
Dashboard
To answer key business questions, the dashboard leverages visual analytics and DAX measures. Key performance indicators (KPIs) such as Gross Profit, Gross Profit Margin, Total revenue and Total Units Sold were created using DAX functions.
Slicers were used to make the dashboard interactive based on region and car models.
Conclusion
This project showcases an end-to-end Power BI workflow spanning data profiling, Power Query ETL, dimensional modeling, DAX measure creation, UI design, testing, and stakeholder communication. Beyond delivering core KPIs, the report establishes clear data lineage by linking all metrics directly to documented transformation rules and validation checks. Ultimately, the dashboard equips JCars with actionable insights to optimize inventory planning, boost branch performance, and maximize profitability.
Top comments (0)