Power Query: Clean Messy Data in Excel Without a Single Formula

Step 3: Make the text consistent

Select the City column, then Transform, Format, Trim, and then Format again, Capitalize Each Word. Repeat both on the Status column.

Trim strips the stray spaces, so " london" and "London " both collapse to plain london and London; Capitalize Each Word then settles the case, turning LONDON and london into London. And a side effect we actively want: the lowercase complete statuses become Complete in the same pass, so one variant problem is already gone.

The stand-in values need Replace Values instead. Right-click the Status column, choose Replace Values, and replace N/A with Unknown.

Repeat for None (Capitalize Each Word already promoted the lowercase none). That handles the written stand-ins, 17 cells in this file. The sneaky ones are the 4 cells with nothing in them at all: leaving Value To Find empty only matches empty text, and a truly empty cell is null, a different thing. Run one more Replace Values and type the literal word null as the value to find. Now all 21 unknowns read Unknown, and a filter or PivotTable will treat them as one group instead of three.

Step 4: Fix the data types

Look at the icon on each column header: ABC means text, 123 means whole number, a calendar means date. Order date is still ABC, which is why those dates were left-aligned in Excel and why they won't sort or plot on a timeline.

Select Order date, then Transform, Data Type, Date. (If Excel asks about locale, pick English (United Kingdom); these are day-first dates.) Then select Amount and set Data Type to Currency; the £ signs and thousands commas parse cleanly.

Dates snap right-aligned as real dates and Amount becomes a number you can actually sum. The Applied Steps pane now reads as a fifteen-step recipe, built in roughly five minutes of clicking. That pane is the real product here; the cleaned grid is just its current output.

Almost every "my total looks wrong" and "my dates won't sort" problem in Excel is a type problem underneath. Text that looks like a number sums to zero; text that looks like a date sorts alphabetically. Fixing types in the query means they arrive fixed, every time, for everyone downstream.

Step 5: Load it back into Excel

Click Home, Close & Load. The cleaned data lands on a new sheet as a proper Excel Table, and the Queries & Connections pane reports the number that matters: 128 rows loaded.

Every city is now one of five clean values, every status one of four, and the Amount column sums to £128,926.33, a figure the text-typed original could not produce at all. This table is ready for a PivotTable, a chart, or a straight copy into the monthly report.

The payoff: next month is one click

A month later the next export lands. Same filename, same folder, now 247 orders deep with September included, six blank rows, and every flavour of mess faithfully back. Ignore all of it. Open your workbook and click Data, Refresh All.

247 rows loaded. The banner is gone, the blanks are gone, the cities and statuses are consistent (46 unknowns standardised this time), the types are set, and you did none of it by hand. The twenty minutes is now zero, this month and every month after.

Common gotchas (and the fixes)

A step errors after new data arrives. Almost always a column renamed or removed at the source. Click the failing step in Applied Steps and read the message; the fix is usually renaming the column back at the source, or editing the step (gear icon) to match the new name.

Replace Values missed some blanks. The empty-versus-null distinction from step 3. An empty Value To Find matches empty text only; truly empty cells are null and need the literal word null typed as the value to find. If a filter still shows a blank group after your replaces, this is why.

Dates come out with day and month swapped. A locale mismatch: a US-formatted source read with UK settings, or vice versa. Delete the type step, then use Transform, Data Type, Using Locale, and name the source's format explicitly.

The editor feels slow on a big file. The editor works on a preview sample (the status bar says so: column profiling is based on the top rows). Build the steps on the sample; the full file processes when you Close & Load, and speed there is rarely a problem below a few hundred thousand rows.

You just cleaned it for the last time

One import, four kinds of fix (rows, columns, text, types), one Close & Load: a 135-row wall of export mess became a 128-row table you can trust, and the September version cleaned itself. The same recipe works on bank exports, CRM dumps, ticket logs, anything that arrives messy on a schedule.

If your team still tidies the same exports by hand every week, our one-day Excel Power Query Introduction course teaches exactly this workflow hands-on, on realistic files, with a trainer in the room for the moment a step goes red.