How to Clean Messy Data in Excel with Power Query

Turn a messy export into a clean table with Power Query — trimming, splitting, unpivoting and merging, in a process you can rerun next month with one click.

How to Clean Messy Data in Excel with Power Query

Every month the same export lands, and every month someone spends forty minutes doing the same find-and-replace, the same text-to-columns, the same delete-the-blank-rows. Power Query exists to end that. You clean the data once, it records what you did, and next month you click Refresh.

It is built into Excel and has been since 2016, sitting on the Data tab under Get and Transform. No add-in, no licence, no macros. This guide walks through the cleanup steps that cover most real exports, in the order you actually apply them.

Quick Facts

  • Power Query records every step, so the same cleanup reruns on next month file with one click.
  • It never modifies your source data. The query reads the file and writes a clean table somewhere else.
  • Remove Top Rows handles the report headers that break every naive import.
  • Unpivot Columns turns a wide cross-tab into the tall format that pivot tables and charts want.
  • If a step fails after a source change, you edit that one step rather than starting again.

Getting the data in

Data, Get Data, and pick your source. From File, From Text/CSV for an export. From Folder if you want to combine every file in a directory, which is one of the genuinely magical features. Drop next month file into the folder and refresh.

At the preview window, always choose Transform Data rather than Load. Load dumps it straight into the sheet unchanged, which is exactly what you are trying to avoid.

The cleanup steps, in order

Order matters more than people expect. Remove junk before you promote headers, and promote headers before you set data types, or the type step will fail every time the file changes shape slightly.

Quick Steps

  1. Remove Top Rows. Strip the report title and blank rows above the real header row.
  2. Use First Row as Headers. Promote the real header row to column names.
  3. Remove Blank Rows and Remove Duplicates from the Home tab.
  4. Trim and Clean. Select the text columns, then Transform, Format, Trim, and again for Clean.
  5. Change Type. Set each column to its correct type, using Using Locale for dates from another region.
  6. Split Column by delimiter for combined fields.
  7. Filter out rows you never want, such as test accounts or disabled users.
  8. Rename columns to something a human can read, then Close and Load.

Trim and Clean. Do this early, always

Trailing spaces and non-printing characters are behind most failed matches later on. Select every text column, apply Trim, then apply Clean. It takes ten seconds and removes a whole class of problem you would otherwise chase for half an hour.

Note that Excel Trim removes leading and trailing spaces only; Power Query Trim does the same. Neither collapses double spaces inside a string. If you need that, use Replace Values with two spaces replaced by one, applied a couple of times.

Splitting columns properly

Right-click a column, Split Column, By Delimiter. The useful part is the options underneath: split at the left-most delimiter, the right-most, or each occurrence. For a full name in the format Surname, Forename, left-most is what you want. For a file path, right-most gets you the filename.

There is also Split by Number of Characters and, in newer versions, By Positions and By Lowercase to Uppercase. The last of which is unexpectedly good at breaking up run-together field names from legacy systems.

Unpivot. The one that feels like a trick

This is the step that converts a report designed for humans into data designed for analysis. If your export has a column per month, with values in a grid, no pivot table will do anything useful with it. Unpivot turns that grid into three tidy columns: the row label, the month, and the value.

Select the columns you want to keep as they are, right-click, and choose Unpivot Other Columns. Choosing it that way round rather than selecting the month columns means the query still works next month when a new column appears.

Merging two exports

The Power Query equivalent of a lookup, and much better at it. Home, Merge Queries, pick the matching column in each, and choose the join type.

Join typeWhat you get
Left OuterEverything from the first list, plus matches from the second. The usual lookup.
InnerOnly rows that appear in both lists.
Left AntiOnly rows in the first list with no match in the second. Your missing records.
Full OuterEverything from both, matched where possible.

Left Anti is the one worth remembering. Reconciling two exports to find what is missing becomes a two-click operation rather than a column of COUNTIF formulas.

Making it repeatable

The whole point is next month. A few habits make the difference between a query that keeps working and one that breaks on the first refresh.

  • Point at a folder, not a file, where the export name changes each month. Combine Files handles the rest.
  • Avoid steps that reference fixed row numbers. Removed Bottom 3 Rows will do the wrong thing when the row count changes.
  • Do not rename columns before you have to. Every rename is a step that fails if the source column name changes.
  • Set data types last, and only on columns you genuinely need typed.
  • Give the query a sensible name in the Queries pane. In six months, Query1 tells you nothing.

When something does break, the error tells you which step failed. Open Advanced Editor or click the step in Applied Steps, fix that one line, and everything downstream picks up again.

🛠️

Gear We Recommend

Working through large exports is far quicker on a machine with the memory to hold them. Here is our admin machine spec.

Browse our Admin Machine picks on Amazon

As an Amazon Associate, TechyGeeksHome earns from qualifying purchases

Glossary

TermWhat it means
QueryA saved sequence of transformation steps applied to a data source.
Applied StepsThe recorded list of transformations, editable individually.
MThe formula language behind Power Query, visible in the Advanced Editor.
UnpivotConverting columns into rows to produce a tall, analysis-friendly table.
MergeCombining two queries on a matching column, similar to a SQL join.
AppendStacking two queries with the same columns on top of each other.
Staging queryA query used only as an input to others, loaded as Connection Only.

Frequently asked questions

Does Power Query change my original file?

No. It reads the source and writes the result to a new table. The source file is never modified, which makes it safe to point at something you did not create.

Do I need a Microsoft 365 subscription?

No. Power Query is built into Excel 2016 and later, including perpetual licences. It was an optional add-in for Excel 2010 and 2013.

What is the difference between Merge and Append?

Merge joins two queries side by side on a matching column, like a lookup. Append stacks them vertically, which is what you want when you have twelve monthly files with the same columns.

Why does my refresh fail with a column not found error?

The source changed its column names or order. Open Applied Steps, find the first step showing an error, and adjust it. This is why renaming columns early is a bad habit.

Can Power Query replace my macros?

For import, cleanup and reshaping work, usually yes, and the result is easier for someone else to maintain. It does not replace macros that drive the Excel interface or interact with other applications.

{"@context":"https://schema.org","@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Does Power Query change my original file?","acceptedAnswer":{"@type":"Answer","text":"No. It reads the source and writes the result to a new table. The source file is never modified, which makes it safe to point at something you did not create."}},{"@type":"Question","name":"Do I need a Microsoft 365 subscription?","acceptedAnswer":{"@type":"Answer","text":"No. Power Query is built into Excel 2016 and later, including perpetual licences. It was an optional add-in for Excel 2010 and 2013."}},{"@type":"Question","name":"What is the difference between Merge and Append?","acceptedAnswer":{"@type":"Answer","text":"Merge joins two queries side by side on a matching column, like a lookup. Append stacks them vertically, which is what you want when you have twelve monthly files with the same columns."}},{"@type":"Question","name":"Why does my refresh fail with a column not found error?","acceptedAnswer":{"@type":"Answer","text":"The source changed its column names or order. Open Applied Steps, find the first step showing an error, and adjust it. This is why renaming columns early is a bad habit."}},{"@type":"Question","name":"Can Power Query replace my macros?","acceptedAnswer":{"@type":"Answer","text":"For import, cleanup and reshaping work, usually yes, and the result is easier for someone else to maintain. It does not replace macros that drive the Excel interface or interact with other applications."}}]}

Disclosure: this post may contain affiliate links. If you buy through one of them, we may earn a small commission at no extra cost to you. We only recommend products we’ve tested or genuinely rate.


Discover more from TechyGeeksHome

Subscribe to get the latest posts sent to your email.

Andrew Armstrong

Andrew Armstrong is a UK-based IT professional with 26+ years of hands-on experience in Windows, Windows Server, SCCM/ConfigMgr, Intune, Active Directory, PowerShell and enterprise infrastructure.

He founded TechyGeeksHome in 2010 and has published 770+ practical guides to real-world IT problems. He also builds free Windows utilities, including Ultimate Settings Panel, which has been downloaded over 850,000 times.

Leave a Reply

Your email address will not be published. Required fields are marked *