How to clean messy CSV exports in Excel with Power Query (step by step)

Every month the same thing happens: you export orders from a shop, a bank, or an accounting tool, and the CSV is a mess. Dates come in as text, there are blank rows, the amount column has currency symbols, and names have trailing spaces. You clean it by hand, and next month you do it all again.

Power Query (built into Excel 2016+ and Microsoft 365, under Data → Get Data) lets you record those cleaning steps once and replay them with one click. Here is a simple, repeatable setup.

1. Load the file as a query, not by opening it

1. Data → Get Data → From File → From Text/CSV.
2. Pick the export file.
3. In the preview, check File Origin (use 65001: Unicode (UTF-8) if accents look broken) and Delimiter.
4. Click Transform Data (not Load). This opens the Power Query Editor.

Tip: put the monthly file in a fixed folder with a fixed name (for example orders-latest.csv) and overwrite it each month. The query keeps pointing at the same path.

2. Fix the header and remove junk rows

• Home → Use First Row as Headers if the headers came in as data.
• Home → Remove Rows → Remove Top Rows if the export has a title block above the table.
• Home → Remove Rows → Remove Blank Rows.
• To drop "Total" rows at the bottom: filter the first column and untick Total (or use Text Filters → Does Not Begin With).

Every click is saved in Applied Steps on the right. That list is your cleaning routine.

3. Trim and clean text

Select the text columns (Ctrl+click), then Transform → Format → Trim, and again Format → Clean (removes non-printing characters). This alone fixes most "why doesn't my lookup match?" problems.

For consistent case: Format → Capitalize Each Word for names, UPPERCASE for codes like SKUs.

4. Turn amounts into real numbers

If amounts look like $1,234.50 or NZ$ 99:

1. Select the column → Transform → Replace Values: replace $ with nothing (repeat for NZ, spaces, etc.).
2. Change the column type to Decimal Number (click the ABC icon in the header).

If your export uses a comma as the decimal separator, right-click the header → Change Type → Using Locale… and pick the matching locale instead of replacing characters.

5. Turn text dates into real dates

Text dates like 03/11/2026 are ambiguous (3 Nov or 11 Mar?). Don't guess:

• Right-click the date column → Change Type → Using Locale… → Data Type Date, Locale e.g. English (New Zealand) for day/month/year or English (United States) for month/day/year.

Then check a few rows where the day is above 12 — if they converted, the locale is right.

6. Split or combine columns

• Split Column → By Delimiter to turn Smith, Anna into two columns.
• Add Column → Column From Examples — type what you want for two rows and Power Query writes the formula. Great for extracting order numbers from messy descriptions.

7. Handle errors instead of hiding them

After changing types, some cells may show Error. Don't just remove them blindly:

• Home → Keep Rows → Keep Errors to see what broke (often a stray header row or a "N/A").
• Fix the cause with a Replace step, then go back to the normal view by deleting the Keep Errors step.

8. Load and refresh

Home → Close & Load puts the clean table on a new sheet. Build your pivots and formulas on that table.

Next month: overwrite the CSV, then Data → Refresh All. Every step replays in order — two seconds instead of twenty minutes.

Common mistakes

• Editing the loaded table by hand. Manual edits are lost on refresh. Make every fix a step in the query.
• Changing types too early. Power Query often auto-adds a "Changed Type" step right after loading. If it guesses wrong on dates, delete that step and set types yourself after cleaning.
• Renaming source columns. If the tool you export from renames a column, the query breaks. Check Applied Steps for the first step with an error — it usually names the missing column.

---

This post was drafted with AI assistance and the steps were checked in Excel.