Mission M09 · Excel · 15 min

Fix messy store names with TRIM and PROPER

Request from your manager

You've been promoted to Junior Analyst! Your first job: a January 2025 export from the old point-of-sale system. Tony, the Jacksonville manager, says his store looks too small in the report, and you suspect it's because some rows say ' SUNSHINE JACKSONVILLE ' instead of 'Sunshine Jacksonville'.

Data

Download the practice file(s):

⬇ sales_dirty.xlsx⬇ sales_dirty.csv

Steps

  1. In Excel go to Data > From Text/CSV, pick sales_dirty.csv and click Transform Data. In the Power Query Editor delete the 'Changed Type' step under Applied Steps so every column stays as raw text, then Home > Close & Load to a table named Dirty.
  2. In a new column store_clean type =PROPER(TRIM([@store_name])). TRIM removes the extra spaces and PROPER fixes the capitalization.
  3. Do the same for categories in a new column category_clean: =PROPER(TRIM([@category])) turns 'frozen' into 'Frozen'.
  4. Compare: =COUNTIF(Dirty[store_name],"Sunshine Jacksonville") vs =COUNTIF(Dirty[store_clean],"Sunshine Jacksonville").
  5. Check that store_clean now has exactly 12 distinct values with =ROWS(UNIQUE(Dirty[store_clean])).
  6. Count all rows (don't remove duplicates yet; that's mission 12).

Check your answer

After cleaning, how many rows belong to Sunshine Jacksonville (all 627 rows, duplicates included)?

Show solution

Before cleaning, COUNTIF finds only 53 rows because 10 more are written ' SUNSHINE JACKSONVILLE '. After TRIM + PROPER all 63 match. TRIM removes leading, trailing and repeated spaces; PROPER capitalizes the first letter of each word.

store_clean:    =PROPER(TRIM([@store_name]))
category_clean: =PROPER(TRIM([@category]))
Count:          =COUNTIF(Dirty[store_clean],"Sunshine Jacksonville")
Distinct check: =ROWS(UNIQUE(Dirty[store_clean]))

Answer: 63 rows

← Monthly sales trend chartTurn text numbers into real numbers →