Power Query vs Classic Excel Cleaning: A Simple Before‑and‑After Example
If you find yourself doing the same cleaning steps every time a file arrives, Power Query can probably do that work for you with a single Refresh click.
In this article we will take a small “dirty” dataset and walk through the classic way of cleaning it with formulas, then show how to rebuild the same logic in Power Query.
Table of Contents
The problem: messy customer list
Suppose you receive a CSV with customer names, regions and revenue, but it has extra spaces, inconsistent region names and a few rows with missing revenue.
Customer Region Revenue
" ACME Ltd" "east " 1200
"Beta Inc" "East" 950
"Gamma Corp " "WEST" <blank>
...
Every month you open this file, fix the same issues and only then start your actual analysis.
Classic approach: formulas in the sheet
One way to fix this is to create helper columns with formulas such as TRIM, UPPER and IFERROR, then copy the cleaned values over the original ones.
- Clean Customer:
=TRIM(A2) - Standardize Region:
=UPPER(TRIM(B2)) - Handle missing Revenue:
=IF(C2="",0,C2)
This works, but after a few months your sheet fills up with helper columns and you have to remember the right steps each time.
Power Query approach: one‑time setup, then refresh
With Power Query, you import the file once, define your cleanup steps in a visual editor and then simply click Refresh when the next CSV arrives.
Step 1: load the file into Power Query
Go to Data > Get Data > From File > From Text/CSV, pick the CSV, check the preview and then choose Transform Data to open the Power Query Editor.
Step 2: trim and clean text columns
In the editor, select the Customer and Region columns, then use Transform > Format > Trim to remove leading and trailing spaces.
Next, with Region still selected, choose Transform > Format > Uppercase so all regions are standardized as EAST, WEST, etc.
Step 3: handle missing revenue
Select the Revenue column and replace blank values with zero via Transform > Replace Values (or by using a simple step in the Applied Steps pane).
You can also change the column type to Decimal to make sure it behaves like a number in later calculations.
Step 4: load the cleaned result back to Excel
Once the data looks right, click Home > Close & Load to return a clean table to a worksheet, separate from the original CSV.
From now on, the only thing you have to do when a new file arrives is put it in the same folder (or point the query to it) and hit Refresh on the query table.
Why bother switching?
On a tiny file, formulas feel faster because you are already comfortable with them, but as soon as you repeat the same cleanup more than a couple of times, Power Query starts to pay for itself.
It reduces the chance of forgetting a step, makes your workbook lighter and gives you a documented list of transformations in the Applied Steps pane instead of scattered helper formulas.
