Spots

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

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

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. The project began with Jcars_data.csv, a flat file containing 32 columns.

Each row represented one vehicle sales order line

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: 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

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

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

For example, if Toyota, totoya and toyta are

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. 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.

Misspelled categories such as totoya and toyta Variations

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

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. 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: Capable of being rerun if the raw export changes The main functions included: This function handled categorical data. Instead of simply replacing one spelling at a time, the function used mapping tables to standardize known variations. This ensured that different spellings of the same business category would be treated as one category in the analysis.

Dates required their own logic because the dataset

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

The function converted these values into a consistent date format so that transactions could be analysed correctly by year, quarter and month. Money required more complex treatment. Identified the currency marker. Removed formatting characters. Detected the M shorthand. Applied the appropriate currency conversion. Returned null for invalid or placeholder values. This meant that monetary fields could eventually be compared and aggregated using one common currency. Additional functions were created for: The result was a cleaning process based on the type of data problem, rather than a long sequence of manual corrections. One of the most important cleaning decisions involved currency. The dataset contained monetary values in different currencies, including: Values without an explicit currency marker were assumed to be KES.

News

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

A Power BI dashboard is only as reliable as the data behind it.

@spots #dev
Source: Dev.to
See more like this