🧹 Tutorial · 8 min read

Power Query Tutorial: Clean Messy Data in Excel Automatically

Power Query is a data cleaning engine built into Excel. You load a messy file once, record the cleaning steps you would otherwise repeat by hand, and then every refresh runs them again in the same order. It is already on your machine, it costs nothing extra, and it needs no add-ins and no AI.

This tutorial walks through the cleaning steps that fix most messy exports: trimming invisible spaces, setting data types, removing duplicates and blank rows, and reshaping wide files. Every button is named exactly as it appears on the ribbon.

What Power Query Actually Is

Power Query ships with Excel 2016 and later, and with Microsoft 365, on Windows and Mac. In newer versions its group on the ribbon is called Get & Transform Data, which is the same feature under another name. Excel for Mac includes it too, although a small number of data connectors remain Windows-only.

Three things separate it from doing the work by hand:

• The steps are recorded. Every move you make appears in the Applied Steps list on the right of the editor. Click a step to see exactly what the data looked like at that moment.
• Nothing is overwritten. The query is a pipeline, not an edit. Your original file stays untouched, and you can delete or reorder steps at any time.
• It repeats. The next time the same export arrives, you save it over the old file and press Refresh. The cleaning you did once runs again on the new rows.

It is not a macro and it is not a formula. If you want to see what it wrote, open Home > Advanced Editor and you will find a short, readable script.

Step 1: Load Your Messy File

Put the file somewhere stable first — a folder you will still use next month. Then in Excel:

• Data > Get Data > From File > From Text/CSV for CSV files, or Data > From Table/Range if the data is already in a sheet.
• In the preview window, check three settings: File Origin (choose 65001: Unicode UTF-8 if the file came from a modern system or a Mac), Delimiter (Comma, Semicolon, Tab, or Colon), and Data Type Detection.
• Click Transform Data, not Load.

Why Transform Data and not Load

Load copies the raw data straight into a worksheet and finishes. Transform Data opens the Power Query Editor, where your cleaning steps get recorded so they can be replayed later. If you only ever need the file once, Load is fine. If you will see this export again, always pick Transform Data.

Step 2: Fix the Top of the File

Exports usually carry five or six lines of report furniture above the real table: company name, report title, generation date. Power Query is showing them as data rows.

• Home > Remove Rows > Remove Top Rows — delete as many rows as needed to bring the real header to the top.
• Home > Use First Row as Headers — promotes that row so the columns get real names.
• Home > Remove Rows > Remove Blank Rows — clears stray empty lines, including any that survived the export.

Two habits save time here. First, remove the top rows before promoting headers, not after — otherwise you promote the wrong row. Second, if the report furniture changes length between exports, do not hardcode a row count; filter the column instead.

Step 3: Trim vs Clean

These two buttons sit next to each other under Transform > Format and they fix different problems. People mix them up constantly.

• Trim removes leading and trailing spaces only. Use it on every text column. It is what makes " 1001" and "1001" the same value again.
• Clean removes non-printable control characters — the invisible codes older systems leave behind. They are invisible on screen but stop text from matching.

Run both, Trim first. Select the text columns, open Transform > Format, and apply each in turn.

When Trim does not fix it: non-breaking spaces

A non-breaking space (character code 160) looks exactly like a normal space but is a different character, and it frequently survives trimming. If two values still will not match after Trim, that is usually why. Two ways to remove it: use Transform > Replace Values and paste the invisible character from a cell in the preview, or add a step in the Advanced Editor with Text.Replace([Column], Character.FromNumber(160), " ").

Step 4: Set the Data Type of Every Column

The icon to the left of each column name shows its type: text, whole number, decimal, date, and so on. Left as Any, values can behave unpredictably when they reach a chart or a PivotTable.

• Use Transform > Data Type, or click the icon on the left of the column header.
• Set number columns to Whole Number or Decimal, date columns to Date, and identifiers that must keep leading zeros (postal codes, account numbers, SKUs) to Text.

After changing a type, values that cannot convert appear as Error. Look at them before removing them: an error is often a real data problem, such as a text note typed into an amount column. Once you have checked them, either Home > Remove Rows > Remove Errors to delete those rows, or right-click the error cell and use Replace Errors to substitute a value.

Step 5: Remove Duplicates and Blank Rows

By this point, every remaining row has been trimmed and typed, so duplicates and blanks are easier to spot and safer to judge.

• Home > Remove Rows > Remove Duplicates compares whole rows. Two rows that differ in one character are both kept.
• To dedupe on a key instead — say, one row per invoice number — select that column first (Ctrl-click for several), then use Remove Rows > Remove Duplicates. With columns selected, the comparison uses those columns only.
• Home > Remove Rows > Remove Blank Rows deletes rows where every cell is empty. If only one column matters, filter that column and untick the blanks instead.

Keep the original export until you have checked the result. Deduplicating a column you did not mean to deduplicate removes real records, and the buttons give no confirmation step.

Step 6: Reshape Wide Files

Report-shaped exports put the months or categories across the columns: Product, Jan, Feb, Mar, and so on. Charts and PivotTables want the opposite shape — one row per observation.

• Transform > Unpivot Columns turns those month columns into two columns, Attribute and Value, without touching a single formula. Select the month columns, then unpivot. Transform > Pivot Column does the reverse.
• Transform > Split Column > By Delimiter breaks "New York, NY" into two columns, or splits a full name.
• Transform > Merge Columns joins columns back together with a chosen separator.

Step 7: Close & Load, and the Point of All This

When the Applied Steps list shows the cleaning you want, choose Home > Close & Load. Use Close & Load To… if you want control: a table on a sheet, a PivotTable, a connection only, or the workbook Data Model.

Now the payoff. Next month, save the new export over the same file path and click Data > Refresh All. Power Query reads the new rows and runs every recorded step again, in order, and the cleaned table updates. Every query in the workbook also lives in Data > Queries & Connections, where you can rename it, edit it, or refresh a single one.

Handling more rows than a worksheet holds

A worksheet stops at 1,048,576 rows, but Power Query can process more than that because the query runs on the data before it is written to a sheet. Load to the Data Model, or use Only Create Connection, when you do not need every row printed into cells. What you display on a worksheet is still capped at Excel's row limit. If you are fighting that limit, see what happens at the Excel row limit and split the file first.

When Power Query Is the Wrong Tool

Power Query rewards repetition. Reach for something lighter when there is no second time.

• A one-off file. Setting up a query to clean a file you will never see again takes longer than fixing it directly. Our free CSV cleaner trims spaces, drops blank rows and columns, removes duplicates, and downloads the result in seconds — in the browser, with no upload.
• No Excel on the machine. Nothing here needs Excel. The cleaner and the AI CSV analyzer work in any browser.
• A file too large for Excel. Power Query can still read it, but the workbook gets heavy. Splitting the file first is often the faster road; the CSV splitter keeps the header on every part.
• Someone else has to reproduce the cleanup. Steps recorded inside a workbook only exist in that workbook. A cleaned CSV file is easier to hand over.

Power Query or AI Cleaning?

They solve different halves of the problem, and the best result usually uses both.

• Power Query is deterministic. Trim, type conversion, deduplication, and unpivot produce the same output from the same input, every time, and you can read the steps to see why. That is what you want for a monthly export and for anything an auditor might ask about.
• AI is better at judgement. Deciding whether "ACME Ltd" and "Acme Limited" are one customer, reading a messy free-text address column, or explaining what looks odd in a file — these are not rule-based tasks.

A practical division of labour: run the mechanical steps in Power Query, export the result, then ask the AI analyzer the judgement questions. If the file is small and once-only, do the mechanical steps in the browser cleaner and skip the workbook entirely.

Frequently Asked Questions

What is the difference between Trim and Clean in Power Query?

Trim removes leading and trailing spaces only. Clean removes non-printable control characters, the invisible codes older systems leave in exported text. They fix different problems, so run both, Trim first.

How do I remove blank rows in Power Query?

Go to Home, then Remove Rows, then Remove Blank Rows. That deletes any row where every cell is empty. If only one column matters, filter that column instead and untick the blanks.

Can Power Query clean a file automatically every month?

Yes, that is the whole point. Power Query records each cleaning step. Save the new export over the same path and click Refresh All, and every step runs again in the same order on the new data.

Is Power Query free, and does it work on a Mac?

Power Query is included with Excel 2016 and later and with Microsoft 365, so there is nothing extra to buy. Excel for Mac includes it as well, though a small number of data connectors remain Windows-only.

Do I need AI to clean data in Excel?

No. Power Query handles the mechanical steps, such as trimming, typing, and deduplicating, and does it the same way every time. Use AI for the judgement calls, like deciding whether two similar records are really the same entity.