The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Cleaning a messy sales file in Power BI is less a fixed sequence of Power Query steps than a set of recorded decisions. For every suspicious value, you can correct it, retain it, flag it, or set it to null, and each choice needs a stated reason. The JCars case, a fictional Kenyan vehicle-sales dataset documented by practitioners in October 2026, makes three points clear: confirm what one row represents before you count anything, keep the original currency before you convert anything, and treat unexplained gaps as findings rather than problems to hide. The figures below are the authors’ own project reports, published on DEV Community on October 3 and 4, 2026. They have not been independently audited.
What the JCars dataset contains, and how far to trust it
The main account, by Asma Salah, says each row represents one vehicle sales order. The fields include customer and vehicle details, pricing and discounts, delivery information, and payment status. A second project account, published the following day under the title “Building a Power BI Solution for JCars Logistics. Raw Data to Business Intelligence,” describes a 276-row, 32-column version of the same fictional business. Its scope is wider: transactions, customers, vehicles, branches, sales representatives, payments, deliveries, logistics costs, and customer experience. A third account, by Mercie Wahome, also dated October 4, 2026, adds project context on investigating and cleaning the data.
As an Amazon Associate I earn from qualifying purchases.
These accounts should not be read as describing one identical file. Their row and column descriptions overlap, but the cleaned outputs and final models differ, as the modeling section below shows. Use the figures as the authors’ reported results for their own versions of the data.
Recommended Free Tools
Step 1: Establish what one row means before counting
Everything downstream depends on row grain. If a row is supposed to be one order, a count of rows and a count of orders should match. In the main account they do not: the file holds 276 rows but only 255 distinct Order IDs. That gap is the first thing to resolve.
#1 Best Overall
- Confirm the intended grain from the data owner or documentation. In this case the author describes one row per vehicle sales order.
- Count rows and distinct Order IDs separately. In Power BI, a distinct count of the order identifier is a measure you can build and check against a plain row count.
- For each duplicated Order ID, compare every column. If the rows are identical, they are probably duplicate records. If payment, delivery, or date fields differ, the rows may be separate events attached to one order, and deleting one would lose real information.
- Record the decision for each group of duplicates: which rows were kept, which were removed, and why.
- Only then decide which measure answers “how many orders.” The author used DISTINCTCOUNT on order IDs in a reported measure, while also noting that duplicate identifiers made them unreliable as a transaction key. A distinct count gives a number, but it cannot repair an identifier that is not unique.
Handling uncertain values: correct, retain, flag, or null
The main account reports several categories of unusual values. The right treatment depends on what the surrounding fields show, so the table lists the options rather than a single fix.
| Issue (as reported) | Possible treatments | Check before choosing |
|---|---|---|
| Negative discount values | Correct the sign only if the source confirms a sign error; retain if the value reflects a real price adjustment; flag otherwise; null if it cannot be explained | Payment status, cancellation, and return fields for the same order |
| Discounts above 100% | Correct only if the field uses a different scale (for example, whole numbers instead of fractions); flag or null otherwise | Whether the discount column uses percentages or decimals across all rows, not just the outliers |
| Missing recorded revenue on paid transactions | Calculate a derived value and label it as derived; keep the blank as recorded; or leave it null | Whether the order is confirmed Paid, and whether unit price, units, and discount are all present |
| Negative revenue on a row with an invalid customer rating | Retain the revenue and null the rating, while flagging the row; exclude the row only from measures that need both fields | Whether the revenue value is itself plausible once returns and cancellations are considered |
For missing revenue, the author calculated values for confirmed Paid orders using unit selling price × units sold × (1 − discount). That formula is a reasonable derivation, but it should be stored in a separate, labeled column so that calculated and recorded revenue remain distinguishable. The author used Power Query to trim and standardize text, correct data types, and address discount issues. The account describes the discount fixes only in general terms, so read them as the author’s judgment for this file rather than a rule to copy.
Mixed currencies: preserve the marker before you convert
The most consequential problem in the main account is currency provenance. The source mixed four currencies: KES, USD, EUR, and ZAR. The currency symbols were removed before the original currency for each row was preserved. The author therefore could not confidently convert all foreign-currency values to KES.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchRank #2
The lesson is that a conversion needs two things the file must still contain: which currency each amount was in, and which rate applies to it. Once the marker is stripped without being recorded, those affected values cannot be converted reliably from the file alone, because nothing in the data says which rate belongs to which amount.
- Before any transformation, add a currency code column and populate it from the symbol or source field.
- Keep the original amount text untouched in a raw column. Strip symbols only in a working column.
- Maintain a separate exchange-rate table with the rate, the date, and the source for each currency pair.
- Convert in a new column, so the original and converted values can be compared and the conversion can be re-run.
- Label any row whose currency cannot be established as unconverted, and exclude it from KES totals instead of guessing.
Reconciling calculated and recorded revenue
The author recalculated revenue from the row-level inputs and compared the result with the recorded values. The residual difference is roughly KES −468.51 million, about 36% of total reported revenue, according to the author’s project report. The author did not change values simply to force agreement. The gap is left as an unresolved limitation.
That choice is the right one to copy, but it has a practical consequence. Calculated and recorded totals should not be presented as interchangeable in the dashboard. A useful next step is to sort the row-level residuals from largest to smallest and group them by payment status and original currency. The account does not test whether the currency problem contributes to the gap, so that link should be checked rather than assumed.
Modeling: choose tables for the questions you need
The two project accounts describe different final models, and the differences are instructive. Neither is a template to combine with the other.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitches| Element | Main account (Asma Salah, October 3, 2026) | Second account (October 4, 2026) |
|---|---|---|
| Fact table | CarSalesFacts | Fact_Sales |
| Date table | DimDate | Dim_Date |
| Other dimensions | DimCarDetails, DimLocation, DimVehicleSpecs | Dim_Branch, Dim_Geography, Dim_SalesRep, Dim_LeadSource |
| Active date relationship | Order Date | Not stated in the account |
| Inactive date relationship | Delivery Date, activated in selected measures with USERELATIONSHIP | Not stated in the account |
The main account’s date handling
Power BI permits one active relationship between two tables. A second date relationship, such as delivery date, can exist as inactive and be switched on inside a specific measure. The main account uses Order Date as the active relationship and calls USERELATIONSHIP in measures that need delivery timing. An illustrative pattern looks like this (table and column names follow the account’s model, but the measure itself is a sketch, not the author’s code):
Delivered Revenue =
CALCULATE(
[Revenue],
USERELATIONSHIP(CarSalesFacts[Delivery Date], DimDate[Date])
)Measures reported in the main account
The reported measures cover revenue, cost, gross profit and margin, distinct-count orders, returns, cancellations, average rating, and year-over-year revenue. Each one depends on the grain and cleaning decisions above. A distinct order count is only as trustworthy as the Order ID, and a revenue measure is only as trustworthy as the reconciliation.
Why the second model differs
The second account organizes its model around sales, branches, geography, sales representatives, and lead sources. That choice follows its analysis questions. The lesson is that model design should follow the analysis you need and the grain you actually have. Adding tables does not make a model better; a dimension only earns its place if a report question depends on it.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Dashboard pages in the main account
The main account describes a dashboard with four areas:
- Executive KPIs, summarizing headline revenue and performance measures
- Sales analysis
- Customer and payment analysis
- Logistics and returns analysis
Because the revenue reconciliation is unresolved, any KPI built on recorded revenue should carry a note that the calculated and recorded totals differ.
Best Value
What this case does and does not establish
No independently published industry statistic or benchmark is attached to this case, so the JCars numbers describe one fictional dataset and should not be generalized to Kenyan vehicle sales or to any population of businesses. The row counts, the 255 distinct Order IDs, and the revenue residual are all figures from the authors’ own project reports. They illustrate the method rather than establishing facts about a real company.
The author’s own reflection captures the central lesson: “This project taught me that cleaning data is never just mechanical, every fix requires a judgment call, and documenting why you made a decision matters as much as the decision itself.” The account identifies her as the author of the project and does not describe a formal expert role, so treat the statement as her reflection on her own work.
The tools named across the accounts are Power BI, Power Query, and DAX. Readers who want to learn them should work from the official Microsoft documentation for each tool, and test the steps above on a small file of their own before applying them to live business data.
Quick Recap
“
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
