DEV Community: Ken Yegon

5 Silent Bugs I Hit Cleaning a Messy CSV in Power BI (and How I Caught Them)

Ken Yegon — Thu, 15 Oct 2026 03:09:58 +0000

I'm a nurse moving into data engineering, and my LuxDev Cohort 9 assessment was to turn a messy vehicle-sales CSV from a Kenyan dealership into a Power BI report. The data had 276 rows and 32 columns, and almost every column was dirty.

The biggest lesson was that nothing in Power BI crashed when I got it wrong. The numbers just came out quietly wrong. Here are the five silent bugs that taught me the most.

Full project: https://github.com/kenkiprotich/jcars-power-bi

What "messy" looked like

Prices as KSh 112,000, USD 238.46, 0.02M, R 19,733.33, or plain text like missing and TBD
Car makes spelled totoya, mitshubishi, v.w
Dates as Excel serial numbers, DD/MM/YYYY, and dd-Mon-yy in the same column
Payment status written 32 different ways

I built 26 standardized columns in Power Query, converted six currencies to KES, and modelled the result as a star schema: one fact table and six dimensions.

Bug 1: The lookup that failed without an error

For car make and model I first used Record.Field with a record of mappings, wrapped in try ... otherwise. The try swallowed every failure, so unmatched values quietly became "Unknown" and I didn't notice.

The fix was a list of pairs and List.Select:

Pairs = {{"toyota","Toyota"}, {"totoya","Toyota"}, {"toyta","Toyota"}},
Match = List.Select(Pairs, each _{0} = Raw),
Result = if List.Count(Match) > 0 then Match{0}{1} else "Unknown"

Then I checked it against every distinct raw value. Lesson: try ... otherwise hides mistakes, so verify the output, not just the absence of errors.

Bug 2: A merge that multiplied my table by more than 200

I merged a dimension table into the fact table on only one key column. Power Query matched every row to every similar row, and my 276-row table became 61,544 rows.

The fix was to select all matching columns in the merge. Lesson: check the row count after every merge. It takes two seconds and would have saved me hours.

Bug 3: Four columns that were secretly the same column

Unit cost, delivery fee, logistics cost and revenue all showed exactly the same numbers as the selling price. I had copied the price-cleaning step four times and never changed the column it read from. All four read [Unit Selling Price].

Nothing errored, because the step was valid. Lesson: when two business columns should differ, check that they do.

Bug 4: The column Power BI refused to sum

My Card visual kept putting the price field into the wrong well. The real problem was that the custom column had no data type, so Power BI treated it as text. The tell is the Σ icon in the Data pane: numeric columns have it, text columns don't.

Bug 5: A join that was too strict

2% of my sales had no branch. I had joined on four fields (region, county, city, branch), and one "Unknown" in any of them broke the match. Since each city has exactly one branch, matching on city alone fixed it, and I documented the assumption.

The numbers still needed judgment

Cleaning isn't only mechanical. Some decisions had no right answer, so I wrote each one down:

Negative revenue on a paid order is a sign typo, so I flipped it. On a cancelled order it became 0, because no sale means no revenue.
Sales with blank revenue were excluded from cost, because including their cost created a fake loss.
Delivery fee is paid by the customer, so I kept it out of profit.

What the data said

Overall margin came out near 13%. Kakamega led on revenue but Eldoret led on profit, and one branch, Thika, lost about 15m KES because its costs were higher than its revenue. I checked whether blank data caused the loss before calling it real. It didn't.

What's still imperfect

About 5% of revenue values are blank, so profit is slightly understated.
Customers aren't de-duplicated, because there was no reliable key.
Exchange rates are fixed, not the rate on each order date.
I haven't tested why Thika sells below cost. Discounting is my guess, not a finding.
The report covers sales, vehicles, branches and reps. Customer, payment and returns pages are still to come.

What I'd tell a beginner

Check row counts after every join.
Check that columns that should differ actually differ.
Never trust try ... otherwise without checking the output.
Write your assumptions down as you go. They are the most valuable part of the documentation.

The report and DAX are in the repo: [GitHub repo link]. If you've hit a silent bug of your own, I'd like to hear about it in the comments.

I had a hard time understanding GIT, until I stopped viewing it as a concept to understand but a tool

Ken Yegon — Sat, 22 Aug 2026 22:17:33 +0000

Pushing repos to github from git was quite hard for me at first.I kept staring at the tutor do it with much ease,commands and expected outputs flawlessly.I was impressed and humbled in equal measure.It seemed like a very smart thing that maybe I wasn't cut out for.I thought maybe I could stick to my medical career. Then I understood one thing, GitHub and Git were just tools and not a concept in development.
By the end of this article, just like me you will have a good understanding of it. The catch though, is you have to relax your mind, don't overthink it , don't complicate it.
Before I understood it, I kept mulling over how experienced techies' github appeared and went back and forth from github checking the very complicated website;the repos branches , the green contibution graph, pull requests were a little too much for my eyes.The first problem I encountered was generating the SSH keys, which i am going to start with.
I didn't understand why Github needed SSH keys when it was password protected, or so i thought.Turns out SSH keys lets my computer and Github trust each other without requiring my password everytime I push(Time is of essence here).I opened Gitbash and ran:
ssh-keygen -t ed25519 -C "kenyegonkiprotich@gmail.com"
A few seconds it gave me this:

Your identification has been saved in /c/Users/User/.ssh/id_ed25519
Your public key has been saved in /c/Users/User/.ssh/id_ed25519.pub
The key fingerprint is:
SHA256:lJy+5vBdz6+QnyWBgrRu7JO4ET++e+QULDlz78q4XK0 kenyegonkiprotich@gmail.com

Along with a little ASCII art image apparently that's just a visual fingerprint, mostly decorative for a beginner like me.
This was the first moment it stopped feeling abstract. I had two actual files on my computer now, a private key and a public key.
To get the key , I ran:
cat /c/Users/User/.ssh/id_ed25519.pub
and copied the output into GitHub , under settings , SSH and GPG keys ,New SSH.
I then went back to Git Bash to test the connection by running:
ssh -T git@github.com
and got confirmation that i had succesfully authenticated.This was the first successes I had.

Now lets move on to the actual Git workflow.I made a practice folder and turned it into a Git repository.On Git Bash ,I ran:

mkdir git-practice
cd git-practice
git init

Git gave this output :

Initialized empty Git repository in C:/Users/User/git-practice/.git/

Then I created this simple file by running:

echo "This is my first Git practice file" > notes.txt

and checked status:

git status

which gave me the output:
Untracked files:
notes.txt

to show that the file was untracked.
This was the working directory.Git could see the file existed but it wasn't tracking it yet.Next I staged it:

git add notes.txt
git status

and the message changed to:

Changes to be committed:
new file: notes.txt

Finally I committed it:
git commit -m "add notes file"

output:

[main (root-commit) 4400b81] add notes file
1 file changed, 1 insertion(+)
create mode 100644 notes.txt

The last step was connecting my local repo to GitHub and pushing it up.I created an empty repository on GitHub , then linked it:

git remote add origin git@github.com:kenkiprotich/git-practice.git

and pushed:

git push -u origin main

Output:
Enumerating objects: 3, done.
Counting objects: 100% (3/3), done.
Writing objects: 100% (3/3), 249 bytes | 249.00 KiB/s, done.
Total 3 (delta 0), reused 0 (delta 0), pack-reused 0
To github.com:kenkiprotich/git-practice.git

[new branch] main -> main branch 'main' set up to track 'origin/main'.

On refreshing the GitHub page , there it was ,visible.

Looking back , I am cut out for this.