What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Cleaning a messy sales file is less about applying a fixed list of fixes than about deciding, value by value, whether the file itself gives you enough evidence to repair it. The JCars case, a fictional dataset about vehicle sales in Kenya, shows that approach well: keep the raw values, record each assumption, and flag anything you cannot repair. The most useful lesson is where repair has to stop. The figures below are those reported by the project authors. They have not been independently audited.
What the JCars project covers
Two DEV Community write-ups describe the JCars dataset, and they do not describe an identical file. The first, by Asma Salah and posted October 3, 2026, treats each row as one vehicle sales order. Its fields include customer and vehicle details, pricing and discounts, delivery, and payment status. It reports 276 rows and 255 distinct Order IDs. The second account, “Building a Power BI Solution for JCars Logistics. Raw Data to Business Intelligence,” posted October 4, 2026, describes a 276-row, 32-column version that spans transactions, customers, vehicles, branches, sales representatives, payments, deliveries, logistics costs, and customer experience. A third account by Mercie Wahome, posted the same day, adds broader project context. Figures from one account should not be assumed to apply to another.
Start with row grain and Order ID reliability
Before counting anything, confirm what one row represents. The first account states that one row is one vehicle sales order. The file, however, has 276 rows and only 255 distinct Order IDs, which means 21 more rows than identifiers. Counting rows would overstate the number of orders. The author used DISTINCTCOUNT for order measures, but also concluded that duplicated identifiers made Order ID unreliable as a transaction key. Those two points together are the core of the identifier problem: a distinct count fixes the arithmetic but does not tell you whether two rows are the same sale.
Separate the duplicates into cases before deleting anything:
#1 Best Overall
- Exact duplicates. Every field matches. To find them in Power Query Editor, select the Order ID column, go to Home > Group By, count rows, and filter for counts above 1. Compare the complete rows for each flagged ID. Keep one copy only after you have logged the removed source row numbers.
- Same ID, different content. These may be entry errors, reissued orders, or genuinely separate transactions. Do not delete them. Carry the original source row number into the model as a row-level key and flag the ID for checking.
- Same ID, conflicting status. Different payment or delivery statuses may represent a status history rather than separate orders. Only the source system can confirm this, so keep both rows and flag them.
Discounts and revenue: check context before calling a value wrong
The file contains negative discount values and discounts above 100%. Neither makes sense as a percentage of price, but both should be read alongside payment status, returns, and cancellations before being labelled errors. An order that was cancelled or returned may carry values that look impossible when viewed on their own, so the surrounding fields are part of the evidence.
Check the scale before applying any rule. A discount stored as 0.15 in one row and as 15 in another looks like two different problems but is a units issue. Confirm the scale across the whole column, then decide how to treat values outside the valid range.
Recomputing missing revenue
The author filled missing revenue only for orders with a confirmed Paid status, using unit selling price × units sold × (1 − discount). That formula is valid only when the discount is a fraction between 0 and 1, so the discount problems have to be resolved first. Work through it in this order:
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 →Rank #2
- Trim and standardize text in Power Query Editor (Transform > Format > Trim), then set data types (Transform > Data Type).
- Resolve the discount scale and mark out-of-range discounts as invalid.
- Filter to Paid orders with blank recorded revenue.
- Calculate revenue in a new column, leaving the original revenue column untouched.
- For rows whose discount is invalid, do not calculate through the error. Flag them instead.
Hypothetical example, not from the dataset: a unit price of 2,000,000 in one currency, 2 units, and a 10% discount give 2,000,000 × 2 × (1 − 0.10) = 3,600,000. The same calculation with a discount of −10% or 110% produces a number that looks plausible but rests on an invalid input.
Some rows combine more than one problem. A row with negative revenue and an invalid customer rating should be flagged for review rather than corrected, because neither value can be verified from the other fields in the file.
Mixed currencies: preserve the marker before anything else
The first account reports that the source mixed KES, USD, EUR, and ZAR. Currency symbols were removed before the original currency for each row was recorded, so the author could not confidently convert every foreign-currency value to KES. This is the most consequential problem in the file. Once a currency marker is removed without being recorded, the number no longer has a reliable currency, and any exchange rate applied to it is a guess. No later step can restore that information for the affected values.
For any future file, add a currency code column before any symbol-stripping step and keep the original amount as recorded. Then handle each situation as follows:
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →| Situation | Recommended action | Reporting note |
|---|---|---|
| Currency recorded per row | Convert using a rate table that carries the rate date and source. | Show the native amount alongside the converted KES amount. |
| Currency recoverable from another reliable field (for example, a payment record that names the currency) | Convert, and record which field supplied the currency. | Label the currency as inferred, not recorded. |
| Currency unknown | Keep the native number, set the converted KES amount to null, and flag the row. | Exclude these rows from KES totals and state how many were excluded. |
Reconciling calculated and recorded revenue
After recalculating revenue, the author compared the results with recorded values. At row level, the residual difference was roughly KES -468.51M, about 36% of total reported revenue. The author did not change values to force agreement and left the difference as an unresolved limitation. The account does not establish the cause. The currency gap is a plausible contributor worth testing, but that is an inference, not a finding.
A reconciliation like this is only useful if it stays visible. Practical habits that follow from the case:
- Keep recorded and calculated revenue in separate columns, with a difference column beside them.
- Confirm the sign convention (calculated minus recorded, or the reverse) before interpreting any residual.
- Break the residual down by currency, payment status, and month so that the unexplained portion has a visible shape.
- Show the open residual on the revenue page of the report, not only in project notes.
Modeling: choose tables for the questions you need to answer
The two accounts describe two different models. They answer different questions and should not be merged into one design.
| Aspect | First account (Asma Salah) | Second account (JCars Logistics Power BI) |
|---|---|---|
| Fact table | CarSalesFacts | Fact_Sales |
| Dimension tables | DimCarDetails, DimLocation, DimVehicleSpecs, DimDate | Dim_Date, Dim_Branch, Dim_Geography, Dim_SalesRep, Dim_LeadSource |
| Date handling | Order Date is the active relationship; Delivery Date is inactive and activated in selected measures with USERELATIONSHIP | Not stated in the project account |
| Emphasis evident in the account | Vehicle details and specifications, location, delivery timing | Branches, geography, sales representatives, lead sources |
| Row grain | One vehicle sales order per row, as stated by the author | Not stated in the project account |
The model design should follow the analysis. A sales order has two meaningful dates, so one of them must be the default and the other must be available on request. A fact table with more dimensions is not better by default; each dimension should answer a question the report actually asks.
Date relationships
A single fact table can relate to the date dimension only once as an active relationship in Power BI. In the first model, Order Date is the active relationship, which suits most sales reporting. Delivery Date is kept as an inactive relationship and used only in measures that need it. The pattern looks like this (illustrative; the column and measure names in your own model will differ):
Best Value
Revenue by Delivery Date =
CALCULATE(
[Total Revenue],
USERELATIONSHIP(CarSalesFacts[Delivery Date], DimDate[Date])
)Core measures
The first account reports measures for revenue, cost, gross profit and margin, distinct-count orders, returns, cancellations, average rating, and year-over-year revenue. Distinct counts are the safe choice for orders when identifiers are duplicated, but only after the identifier questions above are settled. A distinct count of Order ID is illustrated as Orders = DISTINCTCOUNT(CarSalesFacts[Order ID]), with the column name adjusted to your model.
Dashboard pages
The first account’s report includes an executive KPI page, a sales page, a customer and payment page, and a logistics and returns page. Splitting the report this way keeps each page tied to a specific set of questions and to the measures that answer them.
What this case does and does not establish
The counts (276 rows, 255 distinct Order IDs), the currency caveat, and the residual revenue difference come from the project authors’ own reports. None has been independently audited. JCars is a fictional dataset, so its figures say nothing about the vehicle market in Kenya or elsewhere. The project accounts offer no industry benchmark, and none is offered here.
Free tools Windows power users keep installed
One-click scans. No signup required.
The Bottom Line
Treat every cleaning step as a decision you can explain later. Keep raw values, correct only what the file can verify, flag what it cannot, and state unresolved gaps where readers will see them. In her project account, Asma Salah put it this way:
“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.”
That quotation is the author’s own reflection, taken from the project account.
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.
