DEV Community

Ken Yegon
Ken Yegon

Posted on

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

#dataanalysis #powerbi

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"
Enter fullscreen mode Exit fullscreen mode

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

  1. Check row counts after every join.
  2. Check that columns that should differ actually differ.
  3. Never trust try ... otherwise without checking the output.
  4. 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.

Top comments (0)