Introduction
When building a PowerBI solution, a lot of data work must be done before getting to the clean dashboards which give us business insights. For this project, I worked with a JCars Logistics dataset containing 276 rows and 32 columns, covering sales transactions, customers, vehicles, branches, sales representatives, payments, deliveries, logistics costs and customer experience.
The objective was to transform the raw dataset into an analytical Power BI solution that could support management decisions across sales performance, profitability, customer behaviour and logistics operations. We could not use the original data for analysis. We had to clean it, and standardize some things like Order IDs which were not consistently unique. Dates appeared in multiple formats. Quantities were mixed with text. Monetary values used different currencies and formats. Several fields also contained missing, ambiguous or suspicious values.
Understanding the Dataset
Before cleaning anything, I first established the grain of the dataset. Each row represented a sales/order transaction involving a specific customer and vehicle. A transaction could contain more than one vehicle because the Units Sold field could be greater than one.
The dataset contained several groups of business information:
Transactions: Order ID, Order Date, Units Sold
Customers: Customer Name, Customer Type, Customer Age
Geography: Region, County, City
Sales Operations: Branch, Sales Rep, Lead Source
Vehicles: Car Make, Car Model, Vehicle Type, Vehicle Year, Fuel Type, Transmission, Color
Financials: Unit Selling Price, Unit Cost, Discount, Revenue Recorded
Logistics: Delivery Date, Delivery Fee, Logistics Cost, Delivery Status
Payments: Payment Method, Payment Status
Customer Experience: Customer Rating, Review Count, Returned
Order ID looked like the natural transaction identifier, but repeated IDs were assigned to otherwise unrelated transactions. It therefore could not be treated as a reliable unique key.
Data Quality Investigation
I performed a structured data quality audit before making transformations. The main issues identified were:
Missing values and placeholders such as
N/A,NULL,not sureand-Repeated and unreliable Order IDs
Inconsistent category names and capitalization
Mixed date formats and invalid dates
Incorrect data types
Text values inside numeric columns
Suspicious quantities and customer ages
Mixed currencies and monetary formats
Inconsistent discount representations
An exact duplicate check across all columns returned no completely duplicated rows. This distinction was important: repeated Order IDs did not automatically mean duplicated transactions. Removing records based only on Order ID could therefore have removed legitimate business transactions.
Where the available data could not support a correction, I preferred to retain, flag or null the value rather than invent an explanation.
Investigating Inconsistent Categories
Several categorical columns contained multiple representations of the same business value. For example, Toyota appeared as Toyota, toyota, TOYOTA and Toyta. Customer categories showed similar inconsistencies, while sales representative names contained differences in capitalization and possible data-entry errors. Without standardisation, Power BI would treat these as different categories, fragmenting calculations such as revenue, units sold and profitability.
Confirmed variations were standardized while potentially ambiguous labels were investigated before consolidation.
Cleaning and Transformation with Power Query
Rather than modifying the original query directly, I maintained the raw dataset and created a cleaned version for transformation. Power Query was used to standardize categories, convert data types, clean dates, handle monetary values and prepare fields for analysis.
Cleaning Mixed Date Formats
The date columns contained standard dates, Excel serial numbers, missing values, text placeholders, invalid dates and ambiguous date structures. For example, the numeric value 46066 was independently validated as an Excel serial date representing 13 February 2026.
Other values included April 31 2026, 31/02/2026, #DATE!, N/A, not sure and 2026-13-04.
Instead of forcing every value into a date, I created a cleaned date column that attempted several valid conversions and returned null where the date could not be reliably established.
let
Original = Text.Trim([Order Date]),
v = Text.Replace(Original, "Sept", "Sep"),
n = try Number.FromText(v) otherwise null,
UKDate = try Date.FromText(v, [Culture="en-GB"]) otherwise null,
USDate = try Date.FromText(v, [Culture="en-US"]) otherwise null
in
if v = "" then null
else if n <> null and n >= 40000 and n <= 60000 then
Date.AddDays(#date(1899, 12, 30), Number.RoundDown(n))
else if UKDate <> null then UKDate
else if USDate <> null then USDate
else null
Impossible or unverifiable dates were not corrected through assumption. This allowed valid dates and confirmed Excel serial dates to be recovered without creating artificial information from ambiguous records.
Standardising Monetary Values and Currencies
Financial data presented one of the largest data-quality risks. The monetary columns included plain numeric values, KES/KSh, USD, ZAR, abbreviated amounts such as 4.2M, missing/error values and ambiguous values prefixed with ?.
The affected columns were Unit Selling Price, Unit Cost, Delivery Fee, Logistics Cost and Revenue Recorded.
The final reporting currency was Kenyan Shillings (KES). Following the project assumptions, monetary values without an explicit currency were treated as KES. Values expressed using M, such as 4.2M, were interpreted as abbreviated amounts rather than a separate currency.
The Central Bank of Kenya was used as the exchange-rate reference. The project applied:
1 USD = KSh 129.46
1 ZAR = KSh 7.96
Values where the currency itself could not be established were not assigned a currency by assumption.
Handling Suspicious Values
Some values required more than simple cleaning rules. Units Sold, for example, included numeric quantities alongside one, two, 3 cars, NULL, 0 and -1.
Three transactions contained Units Sold = -1. I investigated the surrounding transaction information to determine whether the negative quantity consistently represented a return, cancellation or reversal. It did not.
One transaction was cancelled with zero recorded revenue, another was paid and in transit, while another was marked as returned. Because there was no consistent business explanation, the value was treated as suspicious rather than automatically interpreted as a return.
The same evidence-based approach was used for other questionable values, including customer ages of 121, a negative selling price, discounts above 100%, ambiguous currencies and negative recorded revenue.
Validating the Cleaned Dataset
Cleaning was followed by another validation stage. Power Query profiling tools were used to review column quality, distributions, numeric ranges, category standardisation, null values and transformation errors.
The cleaned dataset retained all 276 source records, with no transformation errors. The increase in null values in some cleaned columns was expected because invalid or unverifiable values had deliberately been converted to null rather than corrected through unsupported assumptions.
Validation also identified 16 transactions where the delivery date occurred before the order date. These records were retained and flagged because the correct dates could not be established from the available information.
Validating Recorded Revenue
I compared recorded revenue against expected revenue based on selling price, quantity and discount. The simple expected-revenue calculation could not consistently reconstruct the recorded revenue.
The validation identified 10 transactions with negative recorded revenue and 21 transactions with zero recorded revenue.
The negative revenue transactions had mixed payment, delivery and return statuses, so there was insufficient evidence to classify them consistently as errors or legitimate reversals. They were retained and flagged. The 21 zero-revenue transactions were associated with cancelled or refunded payments and were retained as plausible business values.
Building the Analytical Data Model
Once the dataset had been cleaned and validated, I moved from data preparation into modelling. The final model separated transactional data from reusable descriptive dimensions.
Fact_SalesDim_DateDim_BranchDim_GeographyDim_SalesRepDim_LeadSource
The relationships followed a one-to-many structure from the dimension tables to Fact_Sales, with single-direction filtering.
An earlier modelling approach explored additional customer and vehicle dimensions and surrogate-key integration. During modelling, I found that some potential dimensions were already close to the transaction-level grain, while merging referenced dimensions back into the fact table introduced unnecessary complexity.
The final model was therefore simplified around dimensions that provided useful analytical grouping and filtering. A more complicated model is not automatically a better model.
Creating Business Measures with DAX
With the analytical model established, I created reusable DAX measures covering sales, profitability, customers, operations and time-based performance.
Sales: Total Revenue, Total Units Sold, Total Orders, Average Order Value, Average Selling Price per Unit
Profitability and Cost: Total Vehicle Cost, Total Logistics Cost, Total Delivery Fees, Gross Profit, Gross Profit Margin %, Profit After Logistics, Profit After Logistics Margin %
Customers: Distinct Customer Names, Average Customer Rating, Total Reviews
Operations: Delivered Orders, Cancelled Orders, Returned Orders, Return Rate %, Cancellation Rate %, Logistics Cost % of Revenue
Time Analysis: Previous Month Revenue, Revenue MoM Change, Revenue MoM %
Total Vehicle Cost =
SUMX(
Fact_Sales,
Fact_Sales[Unit Cost Clean] *
Fact_Sales[Units Sold Clean]
)
A key modelling decision was that Total Orders counted transaction rows rather than distinct Order IDs, because the data-quality investigation had established that Order ID was not a reliable unique identifier.
Recorded revenue was also retained as the primary revenue measure because validation showed that it could not always be reconstructed reliably from selling price, quantity and discount.
Designing the Power BI Report
The report was designed around four main analytical views:
Executive Performance
Sales & Vehicle Performance
Customer & Sales Performance
Operations & Logistics
Rather than creating a separate visual for every possible business question, related questions were grouped into report pages that allowed management to move from high-level performance into specific areas of investigation.
Executive Performance
The Executive view brings together revenue, gross profit, gross profit margin, units sold and orders, supported by analysis across time, vehicle make, branch, delivery status, logistics cost and lead source.
The most important signal was that JCars was generating substantial revenue while recording an overall gross loss, requiring deeper analysis into the sources of profitability.
Customer and Sales Performance
The Customer & Sales Performance page analyses revenue by customer type, sales representative performance, payment methods and statuses, high-value customers and customer ratings.
Dealer customers generated the highest revenue among the customer segments. Instagram and Facebook also emerged as strong visible lead sources by revenue, indicating that acquisition channel plays an important role in sales performance.
However, high revenue alone is not sufficient to determine whether a customer segment or channel is generating sustainable value. Profitability must be considered alongside sales performance.
Operations and Logistics
The Operations & Logistics page focuses on fulfilment and the cost of supporting sales. It includes total orders, logistics cost, logistics cost as a percentage of revenue, delivery status, branch logistics efficiency, regional revenue, returns/cancellations and logistics cost by vehicle.
Of the 275 reporting transactions, 146 (53.09%) were recorded as Delivered. The remaining transactions were distributed across statuses including In Transit, Cancelled, At Yard and Delayed.
The dashboard also showed that Nairobi HQ had the highest visible logistics-cost-to-revenue ratio at approximately 4.7%. This does not automatically mean the branch is inefficient, but it identifies an area that warrants further investigation.
Adding Interactivity
The report was designed to support exploration rather than only display static charts. Interactive features included slicers, cross-filtering, page navigation, custom report tooltips and drill-through analysis.
A dedicated Investigation Details page allows users to move from aggregated results into individual transaction records.
This makes it possible to identify an unusual value in a summary visual and then investigate the underlying transactions without leaving the report.
I also created a custom vehicle tooltip to provide additional performance context while keeping the main report pages uncluttered.
Key Business Insights
1. High Revenue Is Not Translating Into Overall Profitability
JCars Logistics generated approximately KSh1.3 billion in revenue from 454 vehicles across 275 reporting transactions. However, the company recorded approximately KSh83 million in negative gross profit, resulting in a -6.23% gross profit margin.
This indicates that strong revenue generation is not consistently translating into profitability.
2. Vehicle Performance Varies Significantly
SUVs generated the largest share of revenue by vehicle type, while Toyota generated the highest revenue among vehicle makes. However, profitability varied considerably between makes. Vehicle performance should therefore not be assessed using revenue alone.
3. Branch Performance Requires Both Commercial and Operational Measures
Thika Yard generated the highest visible branch revenue. At the same time, Nairobi HQ recorded the highest logistics-cost-to-revenue ratio at approximately 4.7%. The result does not by itself establish poor operational performance, but it provides a clear starting point for investigation.
4. Delivery Performance Remains an Important Operational Area
Only 146 of the 275 reporting transactions (53.09%) were recorded as delivered. A substantial number remained in transit, at the yard, delayed or cancelled. Monitoring where these statuses become concentrated could help management identify operational bottlenecks.
5. Customer Segment and Acquisition Channel Matter
Dealer customers generated the highest revenue among customer segments, while Instagram and Facebook were among the strongest visible lead sources by revenue. These channels should, however, be evaluated alongside profitability rather than revenue alone.
Recommendations
Investigate the Drivers of Negative Gross Profit
Management should investigate the transactions and vehicle categories contributing most to the overall gross loss before making pricing or portfolio decisions.
Review Logistics Performance at Nairobi HQ
The relatively high logistics-cost-to-revenue ratio at Nairobi HQ warrants further investigation to understand the operational activities and costs behind the result.
Monitor Incomplete Delivery Statuses
Delayed, at-yard, cancelled and in-transit orders should be monitored by branch and vehicle category to identify recurring operational patterns.
Evaluate Sales Channels Beyond Revenue
High-revenue customer segments and lead sources should also be evaluated against profitability to determine whether they are generating sustainable business value.
Questions for Further Investigation
Why is the company generating high revenue while recording an overall gross loss?
Do high-revenue vehicle makes also generate sustainable profitability?
Why does Nairobi HQ have a higher logistics-cost-to-revenue ratio than other branches?
Are returns and cancellations disproportionately concentrated among particular vehicle makes after accounting for sales activity?
How much business value is associated with pending and partially paid transactions?
These questions are intentionally framed as areas for investigation rather than conclusions that the available data cannot yet support.
For example, Toyota appears frequently among returns and cancellations, but Toyota also has substantially higher sales activity than many other makes. Raw counts alone are therefore insufficient to conclude that Toyota has a higher return or cancellation risk. A rate-based comparison would be required.
What I Learned
One of the strongest lessons from this project was that data analysis is not simply about cleaning every unusual value until the dataset looks perfect. Some values can be corrected confidently; others cannot. The important part is knowing the difference.
The project required decisions about unreliable identifiers, ambiguous currencies, impossible dates, suspicious quantities, inconsistent categories and financial values that could not always be independently reconstructed.
Instead of forcing every record into a clean business narrative, I used the available evidence to decide whether a value should be:
Standardized
Converted
Retained
Flagged
Set to null
Investigated further
The modelling process provided another useful lesson. An initial model can look technically reasonable and still create unnecessary complexity. Simplifying the final model around dimensions that supported the actual analytical requirements produced a cleaner and more useful structure.
Finally, the project reinforced the relationship between data preparation and business intelligence. Decisions made in Power Query directly affected the DAX measures, and those measures directly affected the conclusions that could responsibly be drawn from the dashboards.
Conclusion
Transforming the raw data into a useful Power BI solution required investigation, cleaning, validation, modelling and careful definition of business logic before meaningful visualisation could begin.
The final solution provides management with visibility across sales, profitability, customers, vehicles, branches and logistics while also allowing users to drill into transactions requiring further investigation.
Project Repository
The complete project, including the Power BI file, dataset, README and supporting screenshots, is available on GitHub:
https://github.com/maggykamau/jcars-logistics-powerbi-analysis
Top comments (0)