INTRODUCTION
I used to think of data cleaning as the "boring part" before the real work of building visuals began. Working on the JCars Logistics dashboard changed that. This article is a walkthrough of the inconsistent columns I found, the Power Query techniques I used to fix them, and the mistakes I made along the way right up through turning that cleaned data into an interactive executive dashboard.
The Setup
The raw dataset (Jcars_data) had 32 columns covering customer info, sales details, delivery logistics, and pricing sourced from what looked like several different people entering data over time, with zero validation rules in place which is a Classic real world data scenario.
Below is a preview of how the data looks like.
Cleaning the messy data.
First, I noticed the columns had no heading and the heading were being used as the first row therefore that was the starting point point using the first row as header.
Columns that didnt have alot of errors in the words I used the find and replace function replacing the blanks, N/A and unknown with null and also replacing places that required blanks with blank.
Challenge encountered was cleaning the columns that had alot of mispelling or wrong formatting. I couldn't use the find and replace option because it would have been time consuming therefore i had to be smart and work around that. I incoporated the use of codes that would help in cleaning. I added a corresponding custom column from where the customr name formula tab i inserted tabs to clean the respective columns.
Case Sensitivity: My First Real Roadblock
Take the Customer Type column. It should have had around 7–8 categories: Individual, Corporate, Government, NGO, Car Dealer, Company, Person, Retail. Instead it had things like; individual, INDIVIDUAL, corp, CORP, CORPORATE, govt, GOVT, n.g.o, N.G.O, Ngo, ngo, DEALER, dealer
I used the following code to be able to clean it
let
Source = Csv.Document(File.Contents("C:\Users\User\OneDrive\Desktop\JCars_Logistics_Dashboard\Jcars_data.csv"),[Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Customer Typer_Clean", each let
NullLike = {"N/A", "NA", "NULL", "Unknown", "UNKNOWN", "-", "", " "},
Cleaned = Text.Clean(Text.From([Customer Type])),
NoExtraSpaces = Text.Combine(List.Select(Text.SplitAny(Cleaned, " "), each _ <> ""), " "),
Trimmed = Text.Trim(NoExtraSpaces),
LowerTrimmed = Text.Lower(Trimmed),
Mapping = [
#"car dealer" = "Car Dealer",
#"dealer" = "Car Dealer",
#"company" = "Company",
#"corp" = "Corporate",
#"corporate" = "Corporate",
#"county govt" = "Government",
#"government" = "Government",
#"govt" = "Government",
#"individual" = "Individual",
#"n.g.o" = "NGO",
#"ngo" = "NGO",
#"non profit" = "NGO",
#"person" = "Person",
#"retail" = "Retail"
],
Result = if List.Contains(NullLike, Trimmed, Comparer.OrdinalIgnoreCase) then null
else try Record.Field(Mapping, LowerTrimmed) otherwise Text.Proper(Trimmed)
in
Result, type text)
in
#"Added Custom"
What the code does
The code loads the raw CSV, promotes the first row to headers, and then adds a new column, Customer Type_Clean, that standardizes every customer type entry into one consistent label. The original column is left untouched, so the raw data is always there to compare against.
For each row, the code works in four stages:
Tidy the text. It strips hidden or non-printable characters, collapses repeated spaces into one, trims the edges, and converts everything to lowercase. This means GOVT, Govt and govt are all treated as the same thing.
Catch blanks. If the value is a placeholder like N/A, NULL, Unknown, or just empty, it becomes a true null instead of showing up as its own category in the dashboard.
Map variants to one label. The cleaned text is looked up in a mapping list. For example, corp and corporate both become Corporate, govt, county govt and government become Government, n.g.o, ngo and non profit become NGO, and dealer becomes Car Dealer.
Fall back gracefully. If a value isn't in the mapping, the code doesn't fail or discard it. It simply returns the text in Proper Case, so unexpected entries are still visible and can be reviewed later.
This would have been helpful since replacing one value with a correct value one at a time would have been time consuming.
Columns like Customer type that needed such i simply just used a code template that would have involved replacing the values.
// ===== FUNCTION: fnCleanText =====
(input as any, mapping as record) as nullable text =>
let
NullLike = {"n/a", "na", "null", "unknown", "-", "", "none"},
AsText = if input = null then null else Text.From(input),
NoNBSP = if AsText = null then null else Text.Replace(AsText, "#(00A0)", " "),
Cleaned = if NoNBSP = null then null else Text.Clean(NoNBSP),
Trimmed = if Cleaned = null then null
else Text.Combine(List.Select(Text.SplitAny(Cleaned, " "), each _ <> ""), " "),
LowerTrimmed = if Trimmed = null then null else Text.Lower(Trimmed),
Result =
if LowerTrimmed = null or List.Contains(NullLike, LowerTrimmed) then null
else if Record.HasFields(mapping, LowerTrimmed) then Record.Field(mapping, LowerTrimmed)
else Text.Proper(Trimmed)
in
Result
// ===== MAIN QUERY =====
let
Source = Csv.Document(File.Contents("FILE_PATH"), [Delimiter=",", Columns=32, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Map_Column1 = [
#"variant1" = "Label1",
#"variant2" = "Label1",
#"variant3" = "Label2"
],
#"Added Column1_Clean" = Table.AddColumn(
#"Promoted Headers", "Column1_Clean",
each fnCleanText([Column1], Map_Column1), type text),
#"Added Column2_Clean" = Table.AddColumn(
#"Added Column1_Clean", "Column2_Clean",
each fnCleanText([Column2], []), type text),
#"Added Column3_Clean" = Table.AddColumn(
#"Added Column2_Clean", "Column3_Clean",
each fnCleanText([Column3], []), type text)
in
#"Added Column3_Clean"
Currency Standardization Challenge
The Problem
JCars operates mainly in Kenya, but its monetary columns did not use a single currency or format. Values appeared as plain numbers, with symbols or codes ($, USD, EUR, ZAR, R 250,000, KES), with stray characters such as "?", and in shorthand such as 2.5M for millions. Some cells held placeholders like "missing", "not available", "error", "#VALUE!" or "TBD". Adding these together as they were would have produced meaningless totals.
Columns Affected
Five monetary columns needed standardizing: Unit Selling Price, Unit Cost, Delivery Fee, Logistics Cost and Revenue Recorded. Discount is a percentage, so it was cleaned separately and not converted.
Currencies Identified
Kenya Shillings (KES), US Dollars (USD, $), Euros (EUR) and South African Rand (ZAR, also written with an R prefix).
Approach
For each monetary column, I added a _Clean column and kept the original for auditing. Each value went through these steps:
Remove hidden characters, extra spaces and "?" symbols.
Convert placeholder text (missing, n/a, error, TBD, etc.) to null.
Detect the currency from the text (EUR, ZAR or an R prefix, USD or $, KES).
Apply the brief's rule: if no currency is indicated, assume the value is in KES.
Extract the numeric part and expand shorthand (M means × 1,000,000).
Multiply by a fixed exchange rate and store the result as a currency type.
Exchange Rates
One fixed set of rates was used across the whole project so that every figure is comparable.
Currency with corresponding Rate to KES
KES=1.00
USD=129.20
EUR=147.67
ZAR=7.84
Assumptions
Values with no currency indicator are in KES.
One fixed rate per currency is applied to every record, regardless of transaction date.
Values that could not be read as a number were set to null rather than guessed.
"M" after a number means millions.
Result
After cleaning, the dashboard uses the converted columns, renamed back to the original names, so all financial analysis is on a common KES basis.
Code used;
(input as any) as nullable number =>
let
// Fixed exchange rates to KES
RateKES = 1,
RateUSD = 129.20,
RateEUR = 147.67,
RateZAR = 7.84,
// Step 1: remove hidden characters, extra spaces and ? symbols
Raw = if input = null then "" else Text.From(input),
Cleaned = Text.Trim(Text.Clean(Raw)),
NoQ = Text.Trim(Text.Replace(Cleaned, "?", "")),
Upper = Text.Upper(NoQ),
// Step 2: placeholders become null
NullLike = {"", "MISSING", "NOT AVAILABLE", "NULL", "ERROR", "-", "N/A", "NA", "#VALUE!", "TBD"},
IsNull = List.Contains(NullLike, Upper),
// Steps 3 and 4: detect currency; no indicator means KES
Rate =
if Text.Contains(Upper, "EUR") or Text.Contains(Upper, "€") then RateEUR
else if Text.Contains(Upper, "ZAR") or Text.StartsWith(Upper, "R") then RateZAR
else if Text.Contains(Upper, "USD") or Text.Contains(Upper, "$") then RateUSD
else if Text.Contains(Upper, "KES") then RateKES
else RateKES,
// Step 5: extract the number and expand M (millions)
NumberOnly = Text.Select(Upper, {"0".."9", ".", "M"}),
HasMillion = Text.Contains(NumberOnly, "M"),
NumStr = Text.Remove(NumberOnly, {"M"}),
Num = try Number.FromText(NumStr) otherwise null,
// Keep negative signs (e.g. refunds)
IsNegative = Text.Contains(Upper, "-"),
Signed = if Num = null then null else if IsNegative then -Num else Num,
// Step 6: convert to KES
Base = if Signed = null then null else if HasMillion then Signed * 1000000 else Signed,
Result = if IsNull or Base = null then null else Base * Rate
in
Result
Date Standardization Challenge
The Problem
The JCars dataset had two date columns, Order Date and Delivery Date, and they did not share one format. The same field contained Excel serial numbers (such as 45367), slash dates (15/03/2024), dash dates with month names (15-Mar-24), ISO-style dates (2024-03-15) and written-out dates (March 15, 2024). Others held placeholders such as "n/a", "TBD", "not sure", "unknown" and "#DATE!". Sorting, filtering by month or calculating delivery times on this data would have given wrong or missing results.
Columns Affected
Two date columns: Order Date and Delivery Date.
Approach
For each column, I added a Clean column and tested every value against each known format in a fixed order. The first format that produced a valid date was used.
Trim and normalize. Remove extra spaces and convert the text to lowercase so comparisons are consistent.
Catch placeholders. Values such as "", "n/a", "na", "null", "#date!", "not sure", "unknown", "tbd", "none" and "-" become null.
Excel serial numbers. If the value is only digits, it is treated as an Excel serial and added to the base date of 30 December 1899.
Slash dates. Values in the form a/b/yyyy are split into three parts. If the first part is greater than 12, it must be the day. If the second part is greater than 12, the first must be the month. If both are 12 or below, the value is assumed to be day/month/year.
Dash dates with month names. For values like 15-Mar-24, the month name is matched on its first three letters, and two-digit years are treated as 20xx.
ISO-style dash dates. Numeric values separated by dashes, such as 2024-03-15, are converted directly.
Written dates. Values like March 15, 2024 are split into month name, day and year.
Combine results. The formats are chained so the first successful parse wins, and anything that matches none of them becomes null.
Set the type. The result is stored as a Date type.
Assumptions
Ambiguous slash dates, where both parts are 12 or below, are day/month/year, which is the common convention in Kenya.
All-digit values are Excel serial numbers.
Two-digit years belong to the 2000s.
Values that could not be parsed by any rule were set to null rather than guessed.
Result
After cleaning, the original date columns were removed and the Clean columns were renamed back to Order Date and Delivery Date. Both columns are now true date fields, so the dashboard can group by month and year, filter by period and calculate delivery times reliably.
Code Used;
(input as any) as nullable date =>
let
raw = try Text.Trim(Text.Clean(Text.From(input) ?? "")) otherwise "",
low = Text.Lower(raw),
// Step 2: placeholders become null
Garbage = {"", "n/a", "na", "null", "#date!", "not sure", "unknown", "tbd", "none", "-"},
MonthMap = {
{"jan",1},{"feb",2},{"mar",3},{"apr",4},{"may",5},{"jun",6},
{"jul",7},{"aug",8},{"sep",9},{"oct",10},{"nov",11},{"dec",12}
},
MonthNum = (name as text) as nullable number =>
let hit = List.First(List.Select(MonthMap, each _{0} = Text.Lower(Text.Start(name, 3))), null)
in if hit <> null then hit{1} else null,
ToNum = (t as text) as nullable number => try Number.From(t) otherwise null,
FixYear = (y as nullable number) as nullable number =>
if y <> null and y < 100 then 2000 + y else y,
// Step 3: Excel serial numbers
IsAllDigits = raw <> "" and Text.Length(Text.Select(raw, {"0".."9"})) = Text.Length(raw),
SerialResult =
if IsAllDigits
then try Date.From(#date(1899, 12, 30) + #duration(Number.From(raw), 0, 0, 0)) otherwise null
else null,
// Step 4: slash dates (a>12 is the day, b>12 is the month, otherwise day/month/year)
SlashParts = Text.Split(raw, "/"),
SlashResult =
if SerialResult = null and List.Count(SlashParts) = 3 then
let
p0 = ToNum(SlashParts{0}),
p1 = ToNum(SlashParts{1}),
p2 = FixYear(ToNum(SlashParts{2})),
Day = if p1 <> null and p1 > 12 then p1 else p0,
Month = if p1 <> null and p1 > 12 then p0 else p1
in try #date(p2, Month, Day) otherwise null
else null,
// Step 5: dash dates with a month name (15-Mar-24)
DashParts = Text.Split(raw, "-"),
DashHasLetters = List.Count(DashParts) = 3 and ToNum(DashParts{1}) = null,
DashMonthResult =
if SerialResult = null and SlashResult = null and DashHasLetters then
let
m = MonthNum(DashParts{1}),
y = FixYear(ToNum(DashParts{2})),
d = ToNum(DashParts{0})
in if m <> null then try #date(y, m, d) otherwise null else null
else null,
// Step 6: numeric dash dates (yyyy-mm-dd, or dd-mm-yyyy)
DashIsoResult =
if SerialResult = null and SlashResult = null and DashMonthResult = null
and List.Count(DashParts) = 3 and not DashHasLetters then
let
IsYearFirst = Text.Length(DashParts{0}) = 4,
y = if IsYearFirst then ToNum(DashParts{0}) else FixYear(ToNum(DashParts{2})),
m = ToNum(DashParts{1}),
d = if IsYearFirst then ToNum(DashParts{2}) else ToNum(DashParts{0})
in try #date(y, m, d) otherwise null
else null,
// Step 7: written dates (March 15, 2024 or 15 March 2024)
WordParts = Text.Split(Text.Replace(raw, ",", ""), " "),
WordMonthResult =
if SerialResult = null and SlashResult = null and DashMonthResult = null and DashIsoResult = null
and List.Count(WordParts) = 3 then
let
DayFirst = ToNum(WordParts{0}) <> null,
m = if DayFirst then MonthNum(WordParts{1}) else MonthNum(WordParts{0}),
d = if DayFirst then ToNum(WordParts{0}) else ToNum(WordParts{1}),
y = ToNum(WordParts{2})
in if m <> null then try #date(y, m, d) otherwise null else null
else null,
// Step 8: the first successful parse wins; anything else is null
Result =
if List.Contains(Garbage, low) then null
else SerialResult ?? SlashResult ?? DashMonthResult ?? DashIsoResult ?? WordMonthResult
in
Result
The Last step was going through the cleaned data and making sure they are in the correct data type respectively.
Now the cleaned data set looked like this;
I then created dimensions and facts table that would assist me in creating a relationship in the model view.
DASHBOARD CREATION
First I had to create DAX functions that would aid in dashboard creation.
My dashboard had a total of 6 pages with each page showing different visualization that would be used in interpretation of the overall JCars Data set,
What the Dashboard Showed
Headline results
The Executive Dashboard summarizes the business in four numbers:
Units sold: 458
Total revenue: Ksh 1.46bn
Gross profit: Ksh 26.43M
Gross profit margin: 1.80%
Revenue is large, but very little of it becomes profit. Everything below explains where the profit comes from and where it leaks.
Page by page findings
- Executive Dashboard. Thika is the highest-revenue branch, followed by Kakamega and Nakuru. Nairobi is the lowest. Toyota leads revenue by make, and the Harrier leads units sold among models.
- Products. Gross profit margin varies sharply by make. Volkswagen and Subaru are strongly positive, while BMW and Isuzu are deeply negative. The returns table shows 149 returned units and 37 cancelled transactions.
- Geography and Sales Team. Rift Valley is the largest region at Ksh 377.47M. Central, Nyanza and Nairobi all have negative margins. Faith Achieng leads the sales team by revenue, ahead of Mary Wanjiku and Brian Otieno. Instagram and Facebook are the top lead sources by revenue.
- Customers and Payments. Car Dealers, Government, NGOs and Corporates make up most revenue. M-Pesa, cash and bank transfer are the largest payment methods. Most revenue sits in the Paid status, followed by Pending and Partial Paid.
- Logistics and Delivery. Total cost is Ksh 1.44bn. Logistics cost is Ksh 27.78M, while delivery fees collected are Ksh 24.91M.
Interpretation: Key Insights
SUVs carry the business. SUVs bring in Ksh 844.86M, or 57.7% of revenue, from 38% of units (175 of 458), at a 9.14% margin. The company-wide margin is only 1.80%, so SUV profit is being offset by losses elsewhere. The business's profit depends heavily on one vehicle type.
Four vehicle types sell at a loss. Sedans lose 16.24% on Ksh 189.68M, Trucks 65.57% on Ksh 58.74M, Vans 27.67% on Ksh 27.48M, and Crossovers 9.84% on Ksh 70.19M. Together they make up 23.6% of revenue and about 34% of units. The dashboard shows where the losses are but not why. Purchase cost, discounting and delivery cost are possible causes that have not yet been tested. Margins also differ sharply by make, but small volumes can exaggerate those swings, so check units per make first.
Three regions with negative margins account for about 35% of revenue. Central (-4.13%), Nyanza (-8.90%) and Nairobi (-16.77%) together bring in Ksh 512.32M. Rift Valley is the largest region, at 25.8% of revenue with a positive 4.58% margin. Nairobi has the lowest branch revenue and the weakest margin, which is unexpected for the capital. Whether the cause is vehicle mix, discounting or cost is not established.
Social media leads are the most profitable; referrals and corporate tenders lose money. Instagram (17.20%), WhatsApp (12.51%) and Facebook (10.09%) have positive margins and the highest revenue per customer, about Ksh 6.1M to 7.0M. Referral (-22.21%) and Corporate Tender (-18.02%) sell at a loss, and Website (-2.95%) and Phone Call (-2.49%) are slightly negative. This does not prove the channel drives margin, since each channel may sell a different vehicle mix.
Revenue depends on a few buyer groups. Car Dealers (23.4%), Government (22.7%), NGOs (20.5%) and Corporates (16.8%) make up about 83% of revenue. Individuals account for only 11.7%. Government and NGO buyers alone are 43.2%, so losing one of these groups would have a large effect.
Returns, cancellations and delivery may weaken the headline figures. The 149 returned units equal roughly one in three of the 458 units sold. One cancelled order still carries Ksh 4.38M of revenue. If returned or cancelled sales are counted in the Ksh 1.46bn, revenue is overstated. Separately, logistics cost exceeds delivery fees collected by about Ksh 2.87M.
Recommendations
Investigate pricing and cost on Sedans, Trucks, Vans and Crossovers. Pull the individual negative-margin sales in these types and compare purchase cost, selling price, discount and delivery cost against profitable SUV sales. Then decide whether to reprice, limit discounting or reduce stock of the worst performers. Trucks come first, given their -65.57% margin.
Review margin performance in Nairobi, Nyanza and Central before changing branch strategy. Compare their vehicle mix and discount levels with Rift Valley and Coast to see what differs. Track monthly margin by region so any fix can be measured.
Review pricing approval for Referral and Corporate Tender sales, and keep investing in Instagram, WhatsApp and Facebook. First test whether the gap comes from the channel or from the vehicles sold through it. If it is the channel, tighten discount approval for tenders and referrals, and monitor margin by lead source each month.
Fix how returns, cancellations and delivery are measured and priced. Reconcile revenue so cancelled and refunded orders are excluded, and confirm what the 149 returned units represent. Review delivery fees against logistics cost, since delivery currently costs more than it recovers.
Protect and diversify the buyer base. Assign clear account ownership for the dealer, government, NGO and corporate accounts that generate about 83% of revenue, and set targets to grow the individual and retail segments.
Conclusion
J Cars Logistics turns over Ksh 1.46bn but keeps only 1.80% as gross profit. Nearly all of that profit comes from SUVs, while Sedans, Trucks, Vans and Crossovers, three weak regions and two lead sources pull it back down. Revenue also depends on a small group of dealer, government, NGO and corporate buyers, and returns, cancellations and delivery costs may be reducing the real figures further.
None of this would have been visible without cleaning the data first. Standardizing every monetary value to KES, converting dates to true date fields and correcting inconsistent categories allowed revenue, cost and margin to be compared on one basis. The next step is to test the causes behind the losses, starting with vehicle level pricing and cost, so management can act on evidence rather than assumption.
Github link:(https://github.com/wainainagabriel63-dev/JCARS-POWERBI-DASHBOARD)
