Funcognito — Explore and learn anything, you got this!

4.2 Power Query: cleaning without retyping

Checked against Microsoft's Excel for Windows keyboard references, August 2026

తెలుగులో చదవండి

What you'll be able to do

Turn a genuinely messy export into a clean table, with every step recorded so it can run again. Next month's file is cleaned by pressing Refresh, with no repeat work at all.

Before you start

Open the messy data workbook. Its dirt is the lesson: junk rows at the top, wrong types, hidden spaces and gaps.

The work

  1. Same either way: load the mess into the editor. Select the data and go to Data → From Table/Range, or open the Power Query editor directly with AltF12. The Power Query window opens, with the data in the middle and Applied Steps down the right side. That steps list is the whole idea. Every action you take is recorded as a step, and the original data is never touched. The recipe plays back from the top whenever you ask.
  2. Same either way: read the steps list as an undo history you can edit. Click any earlier step to see the data as it stood then. Delete a step to unmake it. Rename any step to say what it did. Cleaning stops being destructive and becomes a recipe you can audit.
  3. No documented keyboard shortcut for this — use the menu: the standard opening moves, in order. Go to Home → Remove Rows → Remove Top Rows for the junk lines an export puts at the top. Then Home → Use First Row as Headers to make the real headings into headings. Then fix each column's type using the small icon on its header: text for phone numbers and ID numbers, date for dates, decimal for amounts. Errors after a type change are the data telling you about itself, so click an error to see the value that would not fit.
  4. No documented keyboard shortcut for this — use the menu: now the repair verbs. Each is a right-click on a column, or an action on the Transform tab. Split Column by delimiter separates names at the comma. Transform → Format → Trim and Clean removes the hidden spaces, everywhere at once. Replace Values fixes a mistyped category everywhere. Fill Down handles the report layout where a value appears once and the rows below it are blank, by copying it down its group.
  5. Same either way: finish with Close & Load. The clean result lands on a sheet as a Table. The mess stays in the source file, the recipe lives in the query, and the sheet holds only the result.
  6. Same either way: now the whole point. Next month's equally messy file arrives. Overwrite the source, or drop the new file into the connected folder, and press CtrlAltF5. The recorded recipe runs every step again against the new data. The cleaning was work once, and it is a button from then on.

Try it

Clean the practice mess from start to finish. Top rows out, headers promoted, types set, names split, spaces trimmed, category mistakes replaced, gaps filled down, and the result loaded to a sheet. Then spoil the source with fresh dirt of the same kinds, refresh, and watch the recipe deal with it.

Check yourself

A recorded recipe of every change, which you can edit, and which runs again against the untouched source. Cleaning becomes repeatable and open to audit, instead of one-off surgery on cells.

Useful news. The type was applied, and some values do not fit it. Each error points at a value that needs attention, such as text in an amount column or an impossible date. Click through and decide, rather than quietly putting the type back.

Fill Down on that column. The value is copied down its group, so every row carries its own region, which is what grouping and PivotTables need.

The recipe runs again on refresh. Point the query at the new file, or the new folder, and every recorded step happens again. The work that used to take an afternoon becomes one keystroke.

Go deeper

We haven't checked most of these for screen reader use yet.

Back to Power Query: cleaning without retyping: work through the checklist