A dashboard is only as reliable as the data model behind it.
For JCars, a vehicle business that imports, sells, and delivers vehicles in Kenya, management needs visibility into orders, delivered vehicles, vehicle revenue and gross profit, customer segments, sales-rep performance, payment and delivery statuses, and logistics costs.
The source data contained the fields required for these questions, but it was not immediately suitable for reporting.
The original flat file mixed date formats, currencies, text conventions, and missing values. Order identifiers were inconsistent, categorical values contained multiple representations of the same concept, and some monetary and date fields could not be interpreted reliably without additional rules.
The problem was therefore not simply how to build a dashboard.
It was how to transform an inconsistent operational extract into a reporting model without introducing assumptions that could make the resulting metrics appear more reliable than the source data allows.
This article walks through that process: establishing the analytical grain, profiling and standardising the source data, modelling it as a star schema, defining business measures, and validating the results before they reach the dashboard.
Establishing the Analytical Grain
The first modelling decision is determining what one row represents.
The JCars dataset is modelled at the grain of:
One recorded vehicle order line per row.
A single order line can represent more than one vehicle unit, so order-line count and vehicle-unit count are not interchangeable.
The staging export contains 276 rows. After standardising the order identity, the dataset contains 276 distinct generated order IDs in the staging export.
The project uses the format:
JC-####
for standardised order identifiers.
Data Quality Investigation
Before modelling, every raw column was profiled for:
- Nulls and missing values
- Errors
- Duplicate and inconsistent identifiers
- Inferred data types
- Distinct categorical values
- Inconsistent text conventions
- Implausible values
- Date patterns
- Monetary formats
The resulting issues were handled in the transformation layer while preserving the original values in staging.
This creates two separate concerns:
Staging: What was present in the source?
Reporting layer: What should the analytical model use?
That separation is particularly important when cleaning involves interpretation rather than simple formatting.
Standardising Text Without Removing Business Meaning
Text standardisation addressed whitespace, case differences, and known aliases.
However, values were not merged simply because they appeared similar.
For example, the model keeps:
- Government separate from County Government
- Company separate from Corporate
- Prado TX separate from Land Cruiser
Vehicle attributes were also standardised using contextual mappings.
A Hybrid model suffix, for example, is not removed simply because the fuel-type field contains another value.
The objective is therefore not to make the dataset contain fewer distinct values.
It is to make equivalent values consistent while preserving meaningful business distinctions.
Explicit mapping tables are used where the relationship between source values and reporting values is known.
Where the meaning cannot be established confidently, the original value is retained, and the exception is documented.
Handling Dates
Date fields contained multiple representations, including recognised text formats and spreadsheet serial dates.
Power Query was used to parse known formats and convert valid serial dates into proper date values.
Impossible or unresolved dates were left null rather than being replaced with an assumed value.
An additional validation checks the sequence between order and delivery dates.
Four records contain a delivery date earlier than the order date.
These records are retained as exceptions rather than having the dates automatically swapped.
A reversed date sequence may represent a data-entry problem, a source-system issue, or an incorrectly mapped field. Without source evidence, changing the values would replace an observable data-quality issue with an undocumented assumption.
The resulting model therefore distinguishes between:
- Valid date
- Missing/unresolved date
- Date-sequence exception
This becomes important when calculating delivery lead times.
Currency and Monetary Data
Monetary fields presented a separate modelling challenge.
The source contains:
- KES and KSh
- USD
- EUR
- ZAR
$R?- Currency-less amounts
- Abbreviated values such as
4.2M
Currency and numeric amounts were therefore treated as separate concepts.
Currency-less amounts are assumed to be KES in accordance with the assessment requirements.
Explicit currency indicators are parsed from the source where available.
For cross-order comparison, the model contains an ExchangeRate reference table using a fixed 31 December 2025 reference-rate convention:
| Currency | KES per unit |
|---|---|
| KES | 1 |
| USD | 129.05 |
| EUR | 151.7308 |
| ZAR | 7.8048 |
The converted amounts support comparison within the report but should not be interpreted as the exact KES amount received by the business for each transaction.
The source also contains seven records marked with ? as the currency indicator.
Four selling-price entries were treated as USD and three recorded-revenue entries as EUR based on documented project assumptions. These remain unresolved source-data exceptions and should be confirmed against the original records.
Where currency cannot be established reliably, the value is not assigned an arbitrary exchange rate.
Missing Values Should Not Automatically Become Zero
One of the most important decisions in the transformation process concerns missing monetary and numeric values.
A missing price, cost, or discount does not automatically mean zero.
For example:
Missing cost ≠ KES 0
Missing discount ≠ 0%
Missing revenue ≠ average revenue
Text numerals are parsed where unambiguous. Invalid ages, ratings, and discounts are flagged or left null.
For discounts, No Discount is explicitly mapped to zero. Numeric values between 1 and 100 without a percentage marker are interpreted as percentages.
This distinction prevents missing information from being converted into false business information.
Building the Star Schema
After transformation, the flat structure was modelled into a star schema.
The central fact table is:
Fact_Sales
It contains the recorded order lines, numerical measures, dates, statuses, and foreign keys.
The model uses the following dimensions:
DimDateDimBranchDimSalesRepDimCarModelDimCustomerLocation
The relationships are one-to-many and single-directional, with descriptive dimensions filtering the fact table.
The active reporting date is the cleaned order date.
Delivery-date reporting requires separate relationship handling rather than treating order date and delivery date as interchangeable reporting dimensions.
Why Customer Retention Is Not a KPI Yet
The dataset contains customer attributes but does not contain a reliable customer identifier.
That creates an important modelling limitation.
Without a persistent customer key, the model cannot reliably determine whether two records represent:
- The same customer making multiple purchases
- Two different customers with similar names
- Changes in how a customer was recorded
As a result, the report does not assert unique-customer counts, repeat-purchase rates, retention, or customer lifetime value.
The customer attributes remain available for segmentation analysis, but customer-level longitudinal metrics require a persistent customer identifier.
Defining the Business Measures
The dashboard measures are based on explicit business rules rather than raw column totals.
Orders Recorded
Count of distinct standardised order IDs.
276 without filters.
Gross Cars Delivered
Sum of positive vehicle units on qualifying delivered lines, excluding payment statuses classified as Cancelled or Refunded.
231 delivered units without filters.
This is a unit measure, not an order-line count.
Delivered Order Lines
Count of order lines satisfying the qualifying delivery rule.
140 qualifying lines.
The source contains 146 lines labelled Delivered before the additional payment and unit conditions are applied.
This explains why the raw status count and the KPI do not match.
What the Model Reveals
Once the data is standardised and the measures are defined, the dashboard can be interpreted within the limitations of the model.
Vehicle Volume Is Not Order Volume
There are 276 recorded order lines but 231 qualifying delivered vehicle units.
Toyota accounts for 68 delivered units.
Because one order line can contain multiple units, this does not mean 68 Toyota orders were recorded.
This distinction is a direct consequence of establishing the analytical grain before building the KPI.
Branch Performance Depends on the Reporting Dimension
Kakamega Yard records the highest displayed delivered volume at:
45 delivered vehicle units.
The analysis uses selling branch.
Customer location is modelled separately, so a customer’s region should not be interpreted as the branch responsible for the sale.
This distinction prevents geographical dimensions from being mixed simply because both contain location information.
When One Record Distorts a Category
The Volkswagen profit result illustrates why aggregated metrics require transaction-level validation.
The submitted report shows approximately:
KES 117M
in vehicle gross profit for Volkswagen.
A substantial portion of this result is associated with JC-0095, a transaction containing an unusually high recorded selling price.
The appropriate response is not to remove the transaction because it produces an unexpected result.
Instead, the transaction should be verified against its source invoice and cost evidence.
Until that verification occurs, the Volkswagen result should be interpreted as exception-sensitive rather than as evidence of a stable underlying profitability trend.
This is one reason transaction-level drill-through and exception flags are valuable in an analytical report.
A dashboard can identify an unusual aggregate.
The underlying record is required to explain it.
Date Completeness Limits Cycle-Time Analysis
Of the 276 order lines:
- 145 have a valid order/delivery sequence that can be compared.
- 127 cannot be compared because at least one date is missing or unresolved.
- 4 contain a delivery date earlier than the order date.
The missing dates therefore represent an operational data-quality issue as well as a reporting limitation.
A Status Field Is Not Automatically a Business Event
The return field contains:
- 168 No
- 88 Yes
- 20 Not Provided
These values cannot, by themselves, establish a vehicle return rate. The report therefore displays the field as recorded and preserves Not Provided as a separate category.
A confirmed return-event definition and event-level data would be required before calculating a defensible return KPI.
Remaining Limitations
Several limitations remain in the current model.
The generated order IDs are analytical identifiers rather than the Raw Order IDs.
The return field lacks a confirmed business definition.
Date completeness limits delivery-cycle analysis.
Some currency values remain unresolved.
The fixed exchange-rate table is suitable for the project’s cross-order comparison but does not represent transaction-date exchange rates.
The existing report also does not yet provide sufficient evidence for some requested analyses around customer value, retention, age, ratings, and model year.
These are not necessarily dashboard gaps.
Some are source-data gaps.
Conclusion
The most important step in building a Power BI report is often the work that happens before the first visual is created.
A reliable analytical model requires more than converting text to numbers and creating relationships.
It requires decisions about:
- analytical grain
- identifiers
- business definitions
- missing values
- categorical mappings
- date validity
- currency conversion
- metric eligibility
- relationship design
- and the limits of what the source data can support
The JCars model demonstrates why these decisions matter.
A total can be calculated without being reliable.
A KPI can be technically correct while using an incomplete population.
A category can appear unusually profitable because of a single transaction.
A Yes/No field can look like a ready-made KPI while lacking a clear business definition.
The role of the data model is therefore not simply to make the dashboard work.
It is to make the numbers traceable, explainable, and appropriately qualified before they reach the dashboard.
To see the project, here is the GitHub link : JCars-Logistics-Power-BI.
