How to Clean Data with Power Query in Excel (Step by Step)

Power Query is built into Excel (Data tab, Get and Transform Data) and does the repetitive part of cleaning for you. It never edits your original data: it reads it, applies your recorded steps and writes a clean copy to a new sheet. If you clean a file with the same problems every week, it saves hours.

Step 1: load the data

1. Click any cell in your data and press Ctrl+T to turn it into a table (tick "My table has headers").
2. Choose Data, From Table/Range. The Power Query editor opens.

On the right, the Applied Steps list shows everything you do. You can click any step to see the data as it looked then, rename it or delete it.

Step 2: clean the text

Select the text columns (hold Ctrl and click the headers), then choose Transform, Format:

• Trim removes spaces at the start and end.
• Clean removes non-printing characters.
• Capitalize Each Word fixes ALL CAPS and all lower case. Check names such as McDonald and van der Berg afterwards, as for PROPER in standardising names.

After trimming, compare lengths with a Custom Column such as Text.Length([Name]) against the original if you suspect hidden characters: the non-breaking space and other invisible characters sometimes remain. See removing extra spaces.

Step 3: fix the structure

• Split a column: Home, Split Column, By Delimiter, for example to separate "Smith, John" or an address.
• Fill down: Transform, Fill, Down turns blank cells under a group heading into the heading above (a common result of merged cells).
• Remove blank rows: Home, Remove Rows, Remove Blank Rows.
• Replace values: Transform, Replace Values, for example "N/A" with nothing.
• Remove columns: right-click a header, Remove, or keep only the columns you need with Home, Choose Columns.

Step 4: set the right data types

Click the small type icon in each column header and choose Text, Whole Number, Date and so on. Set ZIP codes, IDs and phone numbers to Text so their leading zeros stay (see keeping leading zeros).

For dates in a different format, use Change Type, Using Locale, and choose the locale the dates were written in. That is how you tell Power Query that 03/04/2026 means 3 April rather than 4 March.

Step 5: remove duplicates

Select the column or columns that identify a row, then Home, Remove Rows, Remove Duplicates. Power Query treats John@x.com and john@x.com as different, because its comparison is case-sensitive. Add a lower-case copy of the key column first (Add Column, Format, lowercase), then remove duplicates on that column.

Step 6: load and refresh

Choose Home, Close and Load. The clean table appears in a new sheet. Next time, paste the new data over the old table (or point the query at the new file) and choose Data, Refresh All. Every recorded step runs again.

When Power Query is not the right tool

• It cannot decide that "Jon Smith" and "John Smith" are the same person, or that a date is ambiguous. Those need a human or a checker that flags them.
• Steps apply to every row the same way. A rule that is right for 99% of rows can quietly damage the rest, so look at the result.
• It cleans one file at a time and has no record of what changed in each cell. DecodingPro lists every change and flags what it was unsure about.

Questions people ask