JCars Logistics Analysis: Data Preparation, Modelling, and Business Insights

Introduction

A Power BI dashboard is only as reliable as the data behind it.
For the JCars Logistics project, the starting point was not a clean analytical dataset. It was a single raw transactional export containing sales, customer, vehicle, branch, payment, delivery, logistics and customer-experience information. The dataset had 276 order lines and 32 columns, but it also contained inconsistent categories, mixed currencies, missing values, different date formats, numbers recorded as words, suspicious business values and an unreliable recorded revenue field.

The objective was therefore not simply to build a visually attractive dashboard. The real task was to take the raw export through a complete analytical process:

Raw data → Investigation → Cleaning → Validation → Modelling → DAX → Dashboard → Analysis → Insights → Recommendations

This article explains that journey, including the reasoning behind the major decisions made along the way.

1. Understanding the Raw Dataset

The project began with Jcars_data.csv, a flat file containing 32 columns.

Each row represented one vehicle sales order line: an order for one or more units of a particular vehicle configuration, sold by a sales representative to a customer through a branch.

The dataset covered areas including:

• Customers
• Locations and branches
• Sales representatives
• Lead sources
• Vehicles
• Prices and costs
• Discounts
• Payments
• Deliveries
• Logistics
• Customer ratings
• Returns

There were 276 order lines representing 466 units sold, with Order Dates spanning 1 January 2025 to 1 December 2026.

At first glance, this looked like a straightforward sales dataset. However, the first important decision was not to immediately start building visuals.

The dataset needed to be investigated first.

Why investigation came first

Building charts before understanding the underlying data can create a polished dashboard containing incorrect information.

For example, if Toyota, totoya and toyta are treated as three separate vehicle makes, the resulting Toyota sales figure would be understated.
Similarly, combining USD, EUR, ZAR and KES values without conversion would make revenue and profitability calculations meaningless.

The first stage therefore focused on finding out what was actually wrong with the raw data.

2. Data Quality Investigation

The investigation revealed that almost every part of the dataset had its own potential data-quality issue.

Some problems were simple formatting inconsistencies, while others could directly affect business calculations.

Examples included:

• Misspelled categories such as totoya and toyta
• Variations such as Mercedes Benz
• Location variations such as cental and nrb
• Model variations such as harier
• Multiple currencies including KES, KSh, USD, EUR, ZAR and R
• Monetary values using M to represent millions
• Missing-value placeholders such as N/A, NULL, TBD, - and #VALUE!
• Excel serial dates and inconsistent date formats
• Units recorded as words such as one, two and three
• Discounts recorded in different formats
• Customer ratings recorded as excellent, 4/5 and 3 out of 5
• Review counts recorded partly as words
• Implausible customer ages
• Typing errors in sales representative names
• Zero logistics costs on delivered orders
• Zero recorded revenue on active orders

The important distinction was that these were not all treated as the same type of problem.

Instead, each issue was considered in terms of how it could affect the analysis.

For example, a misspelled category affects grouping and aggregation, while a mixed currency affects the numerical meaning of the value itself.

This led to a more structured cleaning approach.

3. Cleaning the Data in Power Query

Power Query was used as the main data preparation layer.

Rather than manually correcting individual cells, reusable functions were created for different types of problems.

This was an important design decision because the goal was to make the cleaning process:

• Repeatable
• Transparent
• Easier to audit
• Easier to maintain
• Capable of being rerun if the raw export changes

The main functions included:

fnCat

This function handled categorical data.

Instead of simply replacing one spelling at a time, the function used mapping tables to standardize known variations.

For example:

totoya → Toyota

toyta → Toyota

This ensured that different spellings of the same business category would be treated as one category in the analysis.

fnDate

Dates required their own logic because the dataset contained different date formats as well as Excel serial dates.

The function converted these values into a consistent date format so that transactions could be analysed correctly by year, quarter and month.

fnMoneyKES

Money required more complex treatment.

The function:

1. Identified the currency marker.
2. Removed formatting characters.
3. Detected the M shorthand.
4. Applied the appropriate currency conversion.
5. Returned null for invalid or placeholder values.

This meant that monetary fields could eventually be compared and aggregated using one common currency.

Other functions

Additional functions were created for:

• Order IDs
• Customer Age
• Vehicle Year
• Units Sold
• Discount
• Customer Rating
• Review Count

The result was a cleaning process based on the type of data problem, rather than a long sequence of manual corrections.

4. Standardizing Currency

One of the most important cleaning decisions involved currency.

The dataset contained monetary values in different currencies, including:

Currency	Rate to KES
KES / KSh	No conversion
USD	129.54
EUR	147.84
ZAR	7.93

Values without an explicit currency marker were assumed to be KES.

This was necessary because revenue, selling price, unit cost and logistics cost eventually needed to be combined in profitability calculations.

The conversion was applied consistently throughout the project rather than using transaction-specific exchange rates.

The M shorthand was also handled. For example, a value such as 5M needed to be interpreted as 5,000,000 rather than simply 5.

The order of these operations mattered as well. Currency conversion was performed before downstream calculations so that all monetary values were on a common KES basis.

5. Dealing With Missing and Suspicious Data

Not every unusual value was simply deleted.

This was an important principle throughout the project.

For example, a logistics cost of zero on a delivered order may be technically valid in some circumstances, but from a business perspective it is suspicious enough to investigate.

Similarly, a recorded revenue of zero on an order that was not cancelled or refunded could distort revenue analysis.

These values were therefore flagged rather than silently accepted.

A Data Quality Flag was also introduced to identify records affected by issues such as:

• Missing order dates
• Missing delivery dates
• Estimated selling prices
• Estimated unit costs

Where missing values had to be filled so that calculations could continue, documented defaults were used.

However, the original data-quality issue was still retained through the flag.

This approach avoided a common problem in data cleaning: making the final dataset look complete while hiding how much of it had been estimated or corrected.

6. Rebuilding Revenue Instead of Trusting the Raw Field

One of the most important findings during the investigation was that the recorded revenue field could not simply be taken at face value.

Instead, revenue was independently rebuilt using:

Revenue = Units Sold × Unit Selling Price × (1 − Discount) + Delivery Fee

This created a calculated Revenue field that could be compared against the original Revenue Recorded value.

The reason for doing this was straightforward: if the raw revenue field contained errors, using it directly would carry those errors into every KPI, chart and profitability calculation.

The rebuilt revenue therefore became the basis for the analysis.

This was one of the key lessons from the project:

A financial field in a raw export should be validated against the underlying transaction logic before being used as a core KPI.

7. Data Validation Before Modelling

Cleaning was followed by validation.

The purpose was to confirm that the cleaned dataset was actually ready for modelling.

Several checks were performed.

Type validation

Dates were converted to date types and monetary fields to numeric/currency types.

This prevented numbers from silently remaining as text.

Row-count validation

The cleaned dataset was checked against the original dataset.

The row count remained at 276, confirming that records had not been unintentionally dropped during cleaning.

Business-rule validation

Business rules were used to identify suspicious values.

For example:

• Logistics Cost = 0 on Delivered orders
• Revenue Recorded = 0 on non-Cancelled/non-Refunded orders

Revenue validation

The rebuilt revenue calculation was compared with the recorded revenue.

Category coverage

Mapping tables were built from the distinct values found in the source data so that known variations were accounted for.

Only after these checks was the data considered ready for modelling.

8. Moving From a Flat File to a Star Schema

The cleaned data was initially still a flat transactional table.

That structure was not ideal for the final Power BI model because it mixed transaction-level measures with descriptive information such as customer, vehicle and sales representative attributes.

The next step was therefore to restructure the data into a star schema.

The central table became:

FACT TABLE 2

It retained the transaction-level information, including:

• Dates
• Units
• Prices
• Costs
• Discounts
• Fees
• Payment information
• Delivery information
• Revenue
• Cost of Goods Sold

Dimension tables were created around it:

• DIM-CUSTOMER
• DIM-VEHICLE
• DIM-SALES REP
• DIM-LEAD SOURCE

Each dimension was created using distinct combinations of its relevant attributes and given a surrogate index key.

Why use dimensions?

The purpose was to separate descriptive information from transactional data.

For example, vehicle information such as:

• Make
• Model
• Vehicle Type
• Vehicle Year
• Fuel Type
• Transmission
• Colour

belongs naturally in a vehicle dimension rather than being repeatedly stored as descriptive information in the fact table.

The result was a model that was easier to filter, analyse and extend.

9. Building the Relationships

The fact table was connected to the dimension tables using the surrogate keys.

The relationships were configured as many-to-one relationships from the fact table to the dimensions.

Bidirectional filtering was enabled so that slicers and interactions could work across the report as intended.

This modelling stage was particularly important because the dashboard was designed to support interactive analysis rather than static charts.

A user should be able to select a vehicle type and see the rest of the report respond accordingly.

10. Developing the DAX Layer

Once the model was established, DAX was used to create the core business metrics.

The first measure was Total Revenue:

Total revenue =
SUM('FACT TABLE 2'[Revenue])

Total Units Sold was then used as the main volume measure:

Total Units Sold =
SUM('Jcars_data-CLEANED'[Units Sold])

To analyse profitability, Cost of Goods Sold was calculated at the transaction level:

Cost of goods Sold =
'FACT TABLE 2'[Unit Cost] *
'FACT TABLE 2'[Units Sold]

Gross Profit was then calculated as:

Total Gross Profit =
[Total revenue] -
SUM('FACT TABLE 2'[Cost of goods Sold])

Finally, Gross Profit Margin was calculated as:

Total Gross Profit Margin =
([Total Gross Profit] / [Total revenue]) * 100

These calculations established the core financial logic of the dashboard.

The business definitions were kept straightforward:

Revenue = Units × Price × (1 − Discount) + Delivery Fee

Gross Profit = Revenue − Cost of Goods Sold

Gross Profit Margin = Gross Profit ÷ Revenue

---

11. Designing the Executive Dashboard

With the data and calculations in place, the next challenge was deciding what management should see first.

The Executive Dashboard was designed as a high-level overview rather than an attempt to display every available field.

The first layer consisted of four KPI cards:

• Total Units Sold
• Total Revenue
• Total Gross Profit
• Total Gross Profit Margin

These were chosen because together they provide a quick view of:

volume → revenue → profit → profitability

The remaining visuals were selected to answer the natural follow-up questions:

• Where is profit being generated?
• Which vehicle makes perform best?
• Which branches contribute the most?
• Which lead sources generate profit?
• How are sales distributed across representatives?
• What is happening over time?
• What delivery statuses are associated with sales?

Executive Dashboard screenshot

JCars Executive Dashboard showing the main KPI cards and supporting sales, profitability, branch, lead-source and delivery visuals.

The purpose of this page was not to show every possible analysis. It was to give management a starting point from which deeper questions could be identified.

13. Adding Interactivity With the Drill Through Page.

The report was designed to allow users to move from summary information into more specific questions.

A Vehicle Type slicer was used across the report so that selecting a vehicle category would update the relevant visuals.

Cross-filtering was also enabled between visuals.

Drill-through functionality was incorporated for:

• City
• County
• Fuel Type
• Returned Status

This meant that the report could be used as an investigation tool rather than simply as a presentation of fixed numbers.

For example, if a user noticed an unusual return pattern, they could move from the summary level into more detailed information rather than manually searching through the original dataset.

14. The First Major Finding: Profitability Was Highly Concentrated

Once the dashboard was interactive, the analysis moved beyond simply describing totals.

One of the clearest patterns was the concentration of Gross Profit.

Toyota and Volkswagen together generated approximately 69% of total Gross Profit.

At the branch level, Thika and Nairobi HQ together generated approximately 65% of total Gross Profit.

This changed the question from:

"Which brands and branches sell the most?"

to:

"How dependent is overall profitability on a small number of brands and locations?"

The concentration matters because a problem affecting one of these major contributors could have a much larger financial effect than an issue affecting a smaller contributor.

This became one of the main business-risk observations from the analysis.

15. The Second Finding: Volume and Profit Were Not the Same Thing

SUVs were the leading vehicle type by volume, accounting for 178 of the 466 units sold, or approximately 38%.

However, the analysis showed that volume leadership and profit concentration were not necessarily the same thing.

This distinction is important.

A vehicle category can sell many units without generating the highest amount of profit.

Therefore, inventory decisions based on units sold should not automatically be treated as profitability decisions.

The dashboard made it possible to examine both measures separately.

16. Investigating Lead Sources

The analysis also looked at profitability by lead source.

This revealed an unusual result:

WhatsApp-sourced orders produced an aggregate Gross Profit of approximately −KES 3.49 million.

It was the only lead source in negative territory.

By comparison, Walk-in and Website each contributed more than KES 140 million in Gross Profit.

This raised a business question rather than providing an immediate explanation.

Several possibilities needed to be considered:

• Lower-margin deals may be coming through WhatsApp.
• Discounts may be higher for that channel.
• A small number of unusual transactions may be distorting the total.
• There could be a data-recording issue affecting WhatsApp orders.

For that reason, the appropriate next step is investigation rather than assuming the channel itself is necessarily the problem.

17. Investigating Returns

Another notable finding was the number of records marked:

Returned = Yes: 88 orders, or 31.9%.

This is a large enough proportion to deserve further investigation.

However, the dataset alone does not establish why those returns occurred.

The analysis therefore did not treat the return percentage as proof of a vehicle-quality or fulfilment problem.

Instead, the recommended next step was to segment the returned population by:

• Make
• Model
• Branch
• Sales Representative

and compare it against delivery status and individual vehicle records.

This would help determine whether returns are concentrated in particular areas or simply reflect how the field was recorded.

18. Investigating the Late-2026 Drop

The quarterly analysis revealed another unusual pattern.

Order volume fell sharply after Q2 2026.

The dataset contained:

• Only 1 order in Q3 2026
• 3 orders in Q4 2026

compared with approximately 34–84 orders per quarter through mid-2026.

At first glance, this could look like a major decline in business.

However, the analysis considered the data-collection context as well.

The pattern could be explained by the export having been taken partway through the later quarters.

Because the dataset itself could not prove which explanation was correct, the finding was treated as a data-completeness issue requiring confirmation, rather than automatically reporting it as a genuine business slowdown.

This is an important example of why data analysis requires context.

A chart can show a pattern, but the analyst still needs to determine whether the pattern represents the business or the way the data was collected.

19. Data Quality Became an Analytical Finding

Data quality was not treated purely as a technical cleaning issue.

It became part of the final business interpretation.

Out of the 276 order lines, 76 rows, or 27.5%, carried at least one data-quality flag.

These flags were largely associated with:

• Missing dates
• Estimated selling prices
• Estimated costs

This meant that the dashboard could be used for analysis, but its results needed to be interpreted with the quality of the underlying records in mind.

This is particularly important when looking at small subsets.

For example, a percentage calculated for a single branch, sales representative or month could be heavily affected if only a few records make up that group and some of them contain estimated values.

20. Overall Financial Picture

After cleaning, validation and calculation, the analysis-ready dataset produced the following headline figures:

Metric	Result
Total Units Sold	466
Total Revenue	KES 1,897,538,016
Total Cost of Goods Sold	KES 1,482,051,475
Total Gross Profit	KES 415,486,541
Gross Profit Margin	21.9%
Orders Analysed	276
Data-Quality Flagged Rows	76 (27.5%)
Returned = Yes	88 orders (31.9%)
Delivery Status = Cancelled	37 orders (13.4%)
Payment Status = Cancelled	27 orders (9.8%)

These numbers formed the foundation of the Executive Dashboard and the subsequent business investigation.

21. From Findings to Management Insights

The purpose of the analysis was not simply to identify interesting charts.

The findings were translated into questions management could actually act on.

Profit concentration

Toyota and Volkswagen account for approximately 69% of Gross Profit, while Thika and Nairobi HQ account for approximately 65%.

This highlights concentration across both vehicle makes and branches.

Lead-source performance

Walk-in and Website generated more than KES 140 million each in Gross Profit, while WhatsApp was negative in aggregate.

This creates a reason to investigate how deals from different channels are priced, discounted and recorded.

Data quality

More than a quarter of order lines carried a data-quality flag.

This means that improving the quality of future exports could improve the reliability of management reporting.

Returns

The 31.9% returned rate deserves investigation by vehicle, location and sales representative before conclusions are drawn about the cause.

Volume versus profitability

SUVs lead in units sold, but profitability is more concentrated at the car-make level.

This demonstrates why operational decisions should use the appropriate metric rather than relying on volume alone.

22. Recommendations

The recommendations were directly linked to the findings rather than being generic suggestions.

1. Investigate the WhatsApp lead-generation channel

WhatsApp was the only lead source with negative aggregate Gross Profit.

Before changing investment in the channel, the business should determine whether the result comes from:

• Lower-margin transactions
• Higher discounts
• A small number of outliers
• A data-recording problem

The underlying transactions should be reviewed before deciding what action to take.

2. Review concentration in major vehicle makes and branches

Because Toyota and Volkswagen generate approximately 69% of Gross Profit, and Thika and Nairobi HQ generate approximately 65%, management should understand the level of dependency on these contributors.

This includes reviewing contingency plans around:

• Vehicle supply
• Branch operations
• Staffing
• Operational disruptions

3. Investigate returned orders

The 88 returned orders should be segmented by make, model, branch and sales representative.

This would help determine whether the returns are concentrated in particular parts of the business.

The definition of the Returned field should also be reviewed to make sure it is being recorded consistently.

4. Confirm the late-2026 data completeness

The sharp fall in Q3–Q4 2026 orders should be checked against the original export and data owner.

It should not be used to describe a genuine business slowdown until it has been confirmed that the dataset covers those quarters completely.

5. Improve future data-quality controls

The project identified recurring issues around categories, dates, prices, costs and business-rule inconsistencies.

The reusable Power Query functions created during this project provide a starting point for applying the same controls automatically to future exports.

23. What the Project Demonstrated

The most important lesson from the project was that the dashboard itself was only the final layer.

The majority of the analytical work happened before the first visual was created.

The process moved through several stages:

Raw JCars Export
       ↓
Data Quality Investigation
       ↓
Power Query Cleaning
       ↓
Currency Standardization
       ↓
Business-Rule Validation
       ↓
Independent Revenue Calculation
       ↓
Star Schema Modelling
       ↓
DAX Measures
       ↓
Executive Dashboard
       ↓
Detailed Investigation
       ↓
Business Questions
       ↓
Insights
       ↓
Recommendations

Each stage solved a different problem.

Cleaning made the values usable.

Validation established confidence in the cleaned data.

Modelling created a structure suitable for analysis.

DAX translated business definitions into reusable metrics.

The dashboard made the information accessible.

The investigation turned the visuals into questions.

And the final recommendations connected those findings back to business decisions.

24. Challenges and Lessons Learned

The biggest challenge was the variety of problems in the raw dataset.

There was no single cleaning rule that could solve everything.

Categorical errors required mapping.

Dates required parsing.

Currencies required detection and conversion.

Numbers written as words required transformation.

Missing values required documented treatment.

Business-rule violations required investigation rather than simple replacement.

This led to one of the most useful lessons from the project: data cleaning should be designed around the problems found in the data, not around a generic checklist.

The use of reusable functions such as fnCat, fnDate and fnMoneyKES made the process much easier to maintain than a large collection of manual Power Query steps.

Another important lesson was the importance of independently validating business metrics.

The recorded Revenue field could not simply be assumed to be correct. Rebuilding revenue from units, price, discount and delivery fee provided an independent check and helped expose anomalies that might otherwise have flowed directly into the dashboard.

25. Conclusion

The final JCars Logistics solution transformed a messy flat-file export into an interactive Power BI reporting model.

The final model provided:

• A cleaned and standardized dataset
• Reusable Power Query transformations
• A star-schema data model
• Surrogate-key dimensions
• DAX-based business metrics
• An Executive Dashboard
• A detailed analysis page
• Cross-filtering and slicers
• Drill-through analysis
• Business-quality checks
• Documented assumptions
• Management insights
• Data-driven recommendations

The resulting analysis showed that JCars Logistics generated KES 1.90 billion in revenue and KES 415.5 million in Gross Profit, with a 21.9% Gross Profit Margin across 276 analysed order lines.

More importantly, the project showed where that performance came from and where further investigation was needed: profitability was concentrated in a small number of vehicle makes and branches, WhatsApp-sourced orders were negative in aggregate Gross Profit, returns represented a significant share of orders, and a substantial portion of the dataset required data-quality intervention.

The project therefore moved beyond simply answering "What happened?"

It created a framework for asking:

"Why did it happen, where is it happening, how reliable is the underlying data, and what should be investigated next?"

That is ultimately what turned the JCars dataset from a raw transactional export into a management analysis tool.

GITHUB:https://github.com/gee-999/JCARS-LOGISTIC-ANALYSIS-PROJECT