From Dirty Logistics Data to a Management-Ready Power BI Solution

Introduction

Building a dashboard is only the visible part of a business intelligence project. Before a chart can support a decision, the underlying data must be understood, cleaned, standardised, modelled and validated.

That was the central lesson from my JCars Logistics Power BI project.

The assignment was to develop a complete business intelligence solution for a vehicle sales and logistics company operating through branches in Kenya. The source file contained sales, customers, vehicles, representatives, payment information, delivery activity, logistics cost, returns, ratings and recorded revenue.

It also contained many of the problems analysts meet in real operational data: duplicate identifiers, mixed date formats, invalid calendar dates, inconsistent categories, text inside numeric columns, multiple currencies and financial values that did not always agree.

This article explains how I moved from that raw CSV to a tested Power BI model and an interactive management report.

Understanding the business problem

JCars Logistics needed more than a collection of visuals. Management needed to understand:

• How much active revenue and profit the business generated.
• Which branches and vehicle types contributed the most value.
• Whether deliveries met a reasonable service level.
• Where logistics cost and loss-making orders required attention.
• Which vehicles experienced high returns.
• Whether the source revenue and date fields were reliable enough for decision-making.

I therefore organised the project around a simple analytical flow:

1. Preserve and profile the source.
2. Clean and validate the data in Power Query.
3. Build a dimensional model.
4. Create explicit DAX measures.
5. Design an executive page and supporting analytical pages.
6. Test the entire report.
7. Convert the analysis into findings and recommendations.

The raw dataset

The CSV contained 276 rows and 32 columns covering the 2025-2026 period.

Each row represented an order record or order line associated with a customer, vehicle configuration, branch, sales representative and delivery outcome. Because Units Sold could exceed one, a row did not necessarily represent one individual vehicle.

The first important modelling decision was not to trust Order ID as a technical key. The column contained blanks, placeholders and repeated identifiers. I added Source Row ID from 1 to 276 to create a stable audit key while preserving the original identifier for business reference.

Profiling the data before cleaning

I reviewed every column before making transformations. This prevented me from applying a data type simply because the column name suggested one.

The audit identified 21 significant issue categories, including:

• Order and delivery dates stored as text, Excel serial numbers and invalid calendar dates.
• Delivery dates earlier than order dates.
• Many spellings and formats for the same customer type, branch and region.
• Misspelled sales-representative and vehicle names.
• Text, zero, negative and blank values in Units Sold.
• Prices and costs stored in KES, USD, EUR and ZAR.
• Question-mark prefixes in some financial values.
• Discounts represented as percentages, decimals, whole numbers and text.
• Ratings written as numbers, fractions and descriptive text.
• Return indicators stored as Boolean values, yes/no labels, letters and blanks.
• Recorded revenue that was missing, negative, zero or inconsistent with the other order fields.

The audit changed how I approached the project. Rather than trying to force every source value into a clean category, I decided to preserve uncertainty through nulls, Unknown categories and explicit quality statuses.

Structuring the Power Query workflow

I preserved the original query as Raw JCars Data and disabled its load. I then created Stg JCars Data as a reference query for the transformations.

This structure served two purposes:

• The source remained unchanged and available for comparison.
• Every downstream table could be created from one prepared layer.

I cleaned the data column by column. The main stages were:

1. Add Source Row ID.
2. Trim and clean text.
3. Standardise null markers.
4. Clean order, customer and location fields.
5. Map branches, representatives and lead sources.
6. Standardise vehicle fields.
7. Parse quantities and discount rates.
8. Parse and convert monetary values.
9. Standardise operational status fields.
10. Parse ratings, review counts and return indicators.
11. Clean the date fields.
12. Create calculated and validation columns.
13. Create the fact and dimension tables.

Cleaning one business area at a time made it easier to verify the effect of each step before moving to the next column.

Solving the date problem

The date fields required the most judgement.

The source included examples such as:

• 09-Sept-25
• Excel serial values such as 45914
• 31/02/2026
• April 31 2026
• Ambiguous slash dates such as 01/12/2026

A direct type conversion would either produce errors or interpret some values incorrectly. I used controlled parsing for Excel serials and recognised text formats then documented special decisions at row level.

For example:

• 09-Sept-25 was parsed as 9 September 2025.
• April 31 2026 was corrected to 30 April only when another valid date supported that correction.
• 31/02/2026 was corrected to 28 February 2026 where a valid related date supported the assumption.
• Impossible dates without supporting evidence remained null.
• Valid dates with delivery before order were retained and flagged instead of silently changed.

I created Date Validation Status so the report could distinguish:

• Valid date sequences.
• Missing or invalid order dates.
• Missing or invalid delivery dates.
• Delivery before order date.

The final model identified 48 orders with date-quality issues. These records remained part of the dataset but invalid chronology did not enter the average delivery-duration calculation.

Converting currencies to KES

The financial columns included KES, USD, EUR, ZAR, unlabeled amounts and question-mark prefixes. Aggregating those values directly would have produced meaningless totals.

I separated currency identification from numeric parsing, then applied fixed project rates:

Currency	KES per unit
KES or unlabeled	1.00
USD	130.00
EUR	150.00
ZAR	7.50

The assignment specifically required unlabeled monetary values to be treated as KES.

The question-mark prefix required an additional assumption. Based on the scale and pattern of the affected amounts, I treated it as USD and documented the decision rather than allowing the values to become null without explanation.

The rates were fixed rounded assumptions so the analysis would be reproducible across the dataset. They were not intended to represent the exact exchange rate on every transaction date.

I referred to the Central Bank of Kenya Foreign Exchange Rates page. CBK publishes indicative daily rates based on average market buying and selling rates. The model therefore creates a common analytical reporting currency but it should not replace transaction-date accounting conversion.

Calculating and validating revenue

The dataset already contained Revenue Recorded but I did not assume it was correct. I calculated revenue independently in Power Query:

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

I then compared calculated and recorded revenue and created three validation outcomes:

• Match
• Mismatch
• Cannot Validate

The result was one of the most important findings in the project:

• 116 records matched.
• 123 records mismatched.
• 37 records could not be validated.
• The match rate among validateable records was 48.54%.

Instead of hiding the discrepancy, I made it visible on the Customer and Data Quality page. A dashboard can communicate both business performance and the reliability of the data used to calculate it.

Defining transaction status

The source contained payment, delivery and return information that could produce different interpretations of whether an order should count as active revenue.

I created Final Transaction Status with four mutually exclusive categories:

• Active Sale: 153 orders.
• Returned: 73 orders.
• Cancelled: 42 orders.
• Refunded: 8 orders.

Active commercial KPIs use only Active Sale records. This prevents returned, cancelled and refunded transactions from inflating active revenue and profit.

The Returned Orders measure is deliberately different from the final transaction-status count. It uses the cleaned returned flag, resulting in 88 returned orders among 256 records with a known return status. That distinction is documented because the two fields answer different questions.

Building the data model

The final Power BI model uses a compact star schema:

• FactSalesOrders: 276 rows.
• DimDate: 730 dates covering 2025 and 2026.
• DimBranch: 8 standard branches.
• DimSalesRep: 10 representatives.
• DimVehicle: 35 vehicle combinations.
• _Measures: explicit DAX measures.

The model uses many-to-one relationships and single-direction filtering from dimensions to the fact table.

The active date relationship connects FactSalesOrders[Order Date] to DimDate[Date]. A second inactive relationship connects Delivery Date to the same dimension. Delivery-date measures activate that relationship using USERELATIONSHIP.

This allowed one date table to support both sales analysis and delivery analysis without creating ambiguous filter paths.

Creating the DAX layer

I used explicit measures so the business logic remained visible and reusable.

Active Orders =
CALCULATE(
    [Total Orders],
    FactSalesOrders[Final Transaction Status] = "Active Sale"
)

Active Sales Revenue KES =
CALCULATE(
    SUM(FactSalesOrders[Calculated Revenue KES]),
    FactSalesOrders[Final Transaction Status] = "Active Sale"
)

On-Time Delivery Rate =
DIVIDE(
    [On-Time Delivered Orders],
    [Delivered Orders with Valid Duration]
)

Return Rate =
DIVIDE(
    [Returned Orders],
    [Orders with Known Return Status]
)

Orders by Delivery Date =
CALCULATE(
    [Total Orders],
    USERELATIONSHIP(
        FactSalesOrders[Delivery Date],
        DimDate[Date]
    )
)

Other measures covered gross order revenue, active cost, gross profit, gross margin, average order value, logistics cost, discounts, cancellations, refunds, loss-making orders, revenue year to date and data-quality counts.

One practical DAX lesson was that CALCULATE changes filter context. It does not replace multiplication, subtraction or addition. Row-level financial calculations remained in Power Query, while measures used CALCULATE, SUMX, DIVIDE and time-intelligence functions to respond dynamically to report filters.

Designing the report

The final report contains five visible pages and two hidden support pages.

Executive Overview

The first page provides the management summary. It shows active revenue, gross profit, gross margin, active orders, on-time delivery and average customer rating. Supporting visuals compare branch revenue, monthly revenue and margin, transaction status and vehicle profitability.

Sales and Profitability

This page focuses on revenue, cost, profit, margin, loss-making orders, sales representatives, discounts, customer types and vehicle economics.

Operations and Logistics

The logistics page analyses delivered orders, average delivery days, the on-time rate, delivery status, branch delivery efficiency and logistics cost by vehicle type.

Customer and Data Quality

This page combines customer ratings and returns with revenue-validation and date-quality results. I included data quality in the business report because management should understand the confidence limits around the KPIs.

Insights and Recommendations

The final visible page connects the analysis to decisions through business questions, findings, insights and actions.

The hidden support pages are:

• Branch Details, which receives drill-through context from branch visuals.
• Vehicle Tooltip, which displays vehicle revenue, profit, margin and return rate on hover.

Year, Region, Branch and Vehicle Type slicers are synchronised across the main analytical pages. A page navigator provides consistent movement through the report.

What the analysis found

With all slicers cleared, the final baseline results were:

• 276 total orders.
• 153 active sales orders.
• Approximately KES 905.0M active sales revenue.
• Approximately KES 804.7M active sales cost.
• Approximately KES 99.2M active gross profit.
• 11.65% active gross margin.
• 11 loss-making active orders.
• 113 on-time orders among 123 delivered orders with valid duration.
• 91.87% on-time delivery rate.
• 88 returned orders among 256 known return statuses.
• 34.38% return rate.

The branch analysis showed that Thika Yard led active revenue at approximately KES 171M. Kakamega Yard followed at KES 149M and Athi River Yard at KES 144M. Nairobi HQ recorded approximately KES 54M.

SUVs dominated the vehicle portfolio, producing approximately KES 510M in active revenue and KES 60M in gross profit. That makes SUVs the most important commercial category but it also creates product-concentration risk.

The overall delivery result was strong, but branch performance was uneven. Thika and Kisumu achieved 100% on-time performance. Nairobi HQ recorded approximately 75% and an average delivery duration near 45 days.

Pickup and Hatchback had the highest return rates at approximately 42.86% and 42.42%. The dataset cannot prove why those returns occurred, so the result should trigger investigation by model, supplier, complaint and delivery handling.

Recommendations

I converted the findings into five management recommendations.

1. Protect profitability

Review the 11 loss-making active orders and introduce minimum-margin controls and approval thresholds for unusually high discounts.

2. Improve delivery performance

Conduct operational reviews at Nairobi HQ, Mombasa Port Yard and Athi River Yard. The review should separate routing issues, carrier performance, workload and data-entry delays.

3. Reduce returns

Analyse Pickup and Hatchback returns by model, customer complaint, supplier and delivery handling before changing procurement or inventory decisions.

4. Strengthen data controls

Make order date, delivery date, currency and revenue fields mandatory. Add validation rules before records enter reporting systems so impossible dates and malformed financial values are rejected early.

5. Manage concentration risk

Continue monitoring profitable SUV growth while expanding profitable Sedan, Pickup and Crossover opportunities where the evidence supports them.

Testing the completed report

Before finalising the project, I performed a complete quality-assurance review:

• Refreshed the model and confirmed that no Power Query errors appeared.
• Reconciled baseline measures with all slicers cleared.
• Tested the Year, Region, Branch and Vehicle Type slicers.
• Tested page navigation.
• Tested the Branch Details drill-through page.
• Tested the Vehicle Tooltip on multiple vehicle categories.
• Tested cross-filtering between visuals.
• Reviewed visual, page and report filters.
• Checked for truncated titles, unintended blanks, overlapping visuals and inconsistent formats.
• Confirmed that support pages remained hidden.
• Exported and reviewed the final report PDF.

All final checks passed.

Lessons from the project

The project reinforced several practical lessons.

First, data cleaning is not only technical. Correcting an impossible date requires business reasoning, supporting evidence and documentation. When the evidence is insufficient, retaining null is often better than inventing certainty.

Second, calculated and recorded financial values should be reconciled. A mismatch is not an inconvenience to remove; it can be one of the most important findings in the analysis.

Third, Power Query and DAX solve different problems. Power Query prepared row-level fields and classifications. DAX created filter-responsive measures for the semantic model.

Fourth, interactivity should help answer a question. The branch drill-through and vehicle tooltip were useful because they allowed deeper investigation without overcrowding the main pages.

Finally, the report became more useful when data quality was treated as part of business performance. Management needs to know both what the numbers say and how reliable the underlying records are.

Conclusion

The final JCars Logistics solution is more than a dashboard. It is a documented analytical system that preserves the source, exposes assumptions, validates financial values, separates active from non-active transactions and connects operational evidence to management action.

The project demonstrates the complete Power BI workflow: profiling, Power Query transformation, dimensional modelling, DAX, visual design, interactivity, validation and business communication.

The strongest outcome was not simply producing a set of KPIs. It was building a report in which those KPIs could be traced back to documented rules, cleaning decisions and validation results.

---