Power Query: Clean the Import Once, Then Let It Clean Itself

Here is the difference the whole article turns on.

You get the export in the grid below. You spend forty minutes on it: split the dates, strip the € symbols, fix the casing, delete the Total row, build the pivot. It looks good. You send it.

Next month the same export arrives, and you do it again.

Power Query is the answer to the second sentence. It is not a smarter formula — it is a recorder. You clean the data once, by clicking, and Excel writes down every click as an ordered list of steps. Next month you drop in the new file and press Refresh, and the same forty minutes happens in two seconds, in the same order, without the one step you forgot at 5pm on a Friday.

The catch is that a recording is only as good as what it recorded, and there are about six specific ways a query looks fine in February and returns nonsense in March. Those are section 8, and they are the reason this article is longer than "click Get Data".

Tip: Everything below uses the orders table shown after section 1. Copy it into a blank sheet starting at A1 to follow along. Power Query ships inside Excel from 2016 onward on Windows (Data → Get & Transform Data), and as a free add-in for 2010 and 2013. Excel for Mac can refresh and edit queries in current versions, but the connector list is shorter — check before you build something a Mac colleague has to maintain. The M code blocks are shown so you can read what the clicks produce; you never have to type them.

1) What a Query Actually Is

The mental model that saves the most time: a query is a list of steps, not a copy of your data. Open the editor and the right-hand pane shows Applied Steps — Source, Promoted Headers, Changed Type, and so on down. That list is the query. The table in the middle is just what the list produces when you run it against the current source.

Which means three things follow immediately:

• You can delete a step from the middle. Click the ✕ next to it. Everything below re-runs against the new shape.
• You can re-open a step. Steps created through a dialog get a ⚙ gear icon; click it and the dialog comes back with your settings still in it.
• Nothing is destructive. The source workbook or CSV is never modified. The query reads it and produces a new table somewhere else.

Underneath, the list is a small program in a language called M. Home → Advanced Editor shows it, and it is more readable than its reputation: one let block, one named variable per step, each step taking the previous one as its first argument.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    RemovedTotal = Table.RemoveLastN(Promoted, 1)
in
    RemovedTotal

Read it bottom-up if it helps: the query returns RemovedTotal, which is Promoted minus its last row, which is Source with its first row lifted into headers. You will rarely write M from scratch. You will regularly open this window to fix a hard-coded file path or a column name, which is worth the ten minutes it takes to learn to read.

One thing to know before your first query: M is case-sensitive, and its function names are not localised. A Spanish or German Excel shows Spanish or German ribbon buttons, but the code underneath still says Table.PromoteHeaders. That is a mercy — it means a query built on one language's Excel opens correctly on another.

2) Getting the Data In

Three entry points cover almost everything:

From a range in this workbook — select any cell in the data and click Data → From Table/Range. Excel will insist on converting the range to a Table first (Ctrl+T); let it, and rename the Table to something meaningful in the Table Design tab before you build the query, because the name is baked into the Source step.

From a file — Data → Get Data → From File → From Text/CSV (or From Workbook). You get a preview with three dropdowns: delimiter, file origin (the encoding — this is where a UTF-8 export full of é gets fixed), and data type detection.

From a folder — Get Data → From File → From Folder. This is the one that changes how you work. Point it at a folder of monthly exports and you get one table listing the files; click Combine and Power Query builds a sample query for one file, applies it to all of them, and stacks the results. Next month you save the new file into the folder and press Refresh. Nothing else.

🎯 Scenario: Twelve monthly CSVs with identical layouts, currently pasted one under another by hand every January.

A folder query replaces the paste entirely — and it adds a Source.Name column carrying the file name, so you still know which month each row came from. If the file names carry the period (orders-2026-02.csv), that column is also your date dimension, one Split Column away.

Two things to set before you leave the entry step. First, turn on View → Column quality and Column distribution; the little bars under each header show the percentage of valid, error and empty values, which is the fastest way to find the dash sitting in the Qty column. Second, know that profiling is calculated on the top 1,000 rows by default — there is a toggle in the status bar to switch it to the whole column, and on a 200,000-row export the default will happily tell you a column is 100% clean when row 40,000 is not.

3) The Six Steps That Do Most of the Work

Nearly every cleaning job is some ordering of these.

Use First Row as Headers (Home → Use First Row as Headers). Turns row 1 into column names. Its opposite exists too, which matters when an export has two header rows.

Remove Rows (Home → Remove Rows). The Total row in A11 goes with Remove Bottom Rows → 1. Careful: that step removes the last row, whatever it is. If next month's export has the total in the middle, or two junk rows, the count is wrong. The sturdier version is Home → Remove Rows → Remove Blank Rows plus a filter on the Order column: click its dropdown, Text Filters → Does Not Equal → Total. A filter is described by what it matches, so it keeps working when the row moves.

Transform → Format → Trim and Clean. Trim removes leading and trailing whitespace — this is what fixes " nordwind gmbh ". One difference worth committing to memory: Excel's TRIM also collapses runs of internal spaces down to one, and Power Query's Trim does not. "Rivera y Cia" stays double-spaced. If internal doubles matter, use Replace Values with two spaces → one space, and run it twice for the paranoid case of three.

Clean strips non-printable control characters. Like Excel's CLEAN, it does not touch the non-breaking space (character 160) that web and SAP exports are full of — for that, Replace Values, pasting the invisible character into the Find box.

Transform → Format → lowercase / UPPERCASE / Capitalize Each Word. Capitalize Each Word is Power Query's PROPER, and it shares PROPER's flaw: it turns nordwind gmbh into Nordwind Gmbh and Rivera y Cia into Rivera Y Cia. Neither is the company's actual name. Casing is fine for making a grouping key consistent; it is not fine for producing a label a customer will read. Section 6 has the honest fix.

Split Column (Home → Split Column → By Delimiter / By Number of Characters / By Positions). The delimiter dialog has an easily-missed option: split at the left-most, right-most, or each occurrence. Right-most is what you want for Surname, First, Middle-style data where only the last comma is reliable.

Remove Duplicates (Home → Remove Rows → Remove Duplicates). Select the columns that define a duplicate first; with no selection it removes rows identical across every column, which is almost never the rule you meant. And know which one survives: Power Query keeps the first occurrence in the current sort order, so sort before you dedupe if "latest wins".

🎯 Scenario: The orders export, cleaned down to something a pivot can read.

Promoted Headers          → row 1 becomes column names
Filtered Order ≠ "Total"  → junk row gone, and gone next month too
Trimmed Customer          → "  nordwind gmbh " → "nordwind gmbh"
Lowercased Customer       → one grouping key per company
Replaced "-" with "0"     → the Qty column can now become a number

Five clicks, and the sixth — Refresh — is free forever.

4) Types, and the Locale Trap

Every column in Power Query has a data type, shown as a small icon left of the header: ABC for text, 123 for whole number, 1.2 for decimal, a calendar for date. Types are not cosmetic. Qty typed as text will not sum. Order Date typed as text will not group by month.

Change one with Transform → Data Type, and here is the trap. Your Excel converts text to numbers and dates using your machine's regional settings. The export in the grid was written by a system in Germany, so it uses a comma decimal separator and dots in dates. On a UK or US machine, a plain Change Type turns 18,40 into 1840 — not an error, just a number a hundred times too big, which is the worst possible failure because nothing goes red.

The fix is one menu deeper: Transform → Data Type → Using Locale. Pick the target type, pick the culture the data was written in (German (Germany)), and Power Query parses it that way regardless of your machine.

Two more things stand between € 18,40 and a number. The currency symbol and the space are not part of any locale's number format, so strip them first with Replace Values (€ → nothing), then convert using German (Germany).

= Table.TransformColumnTypes(RemovedSymbol, {{"Unit Price", type number}}, "de-DE")

🎯 Scenario: One date column holding three formats — 03/02/2026, 2026-02-04 and 05.02.2026.

No single locale reads all three, and this is where people give up and go back to formulas. The answer is Add Column → Custom Column, with a chain that tries each culture and falls through:

= try Date.FromText([Order Date], [Culture="de-DE"])
  otherwise try Date.FromText([Order Date], [Culture="en-GB"])
  otherwise null

German first, because it is the culture that reads 05.02.2026; ISO strings like 2026-02-04 parse under either. try ... otherwise is M's error handler, and it is the single most useful thing in the language — an error in one row is caught and replaced instead of poisoning the column.

Note what makes this safe here: both cultures read 03/02/2026 as day-first, so whichever branch catches it, the answer is 3 February. Add a US export to the same folder and that stops being true — 03/02/2026 becomes 2 March, silently. When formats genuinely mix from different regions, the only correct fix is to split the sources apart and convert each with its own culture.

The blunter instrument, for a column that is mostly clean: Transform → Replace Errors. It swaps every error value in the selected columns for something you choose. Replacing the dash in Qty with 0 is the honest version of that — a missing quantity really is zero shipped units, and writing it down as 0 is a decision, not a hack.

Compare the formula route on the same column, and the contrast is the whole argument:

=SUM(--E2:E10)

Result: #VALUE! — because E5 holds a dash. One bad cell takes down the entire array. In the query, Replace Errors handles it row by row, and the eight good rows come through untouched.

5) Unpivot: the Transform With No Formula Equivalent

Most Power Query steps have a formula you could have written instead. Unpivot does not, and it is the reason a lot of people install it.

Reports arrive as crosstabs — one row per thing, one column per month:

That shape is readable and completely useless to a PivotTable, which wants one row per observation. Select the three month columns, then Transform → Unpivot Columns, and you get:

Nine rows from three, in one click. Rename Attribute to Month and Value to Sales and you have a table you can pivot, filter and chart properly.

🎯 Scenario: The same report, but April arrives next quarter and adds a column.

Select the month columns and Unpivot, and the step records those three names. April is not in the list, so it stays a column and quietly falls out of your totals. Instead select the Customer column and choose Transform → Unpivot Other Columns. Now the step records "everything except Customer" — and every future month is included the day it appears. Unpivot Other Columns is almost always the one you want; the plain version is a trap dressed as the obvious choice.

The reverse exists too — Transform → Pivot Column — for the rarer case where a system gives you attribute/value pairs and you need them side by side.

6) Merge and Append: Joining Without VLOOKUP

Two operations, constantly confused. Append stacks tables vertically — same columns, more rows. Merge joins them horizontally — matching rows, more columns.

Append Queries (Home → Append Queries) is copy-paste that refreshes. It matches on column names, not positions, so a source whose columns arrive in a different order still lines up correctly. A column present in one table and missing from another produces null in the rows that lacked it, which is exactly right and also exactly how you discover that last month's export called it Customer and this month's calls it Customer Name.

Merge Queries is XLOOKUP for whole tables. Pick the left table, pick the right, click the column in each that they join on, and pick a join kind. There are six, and choosing wrongly is the most common Merge bug:

Left Outer is the default and the right answer maybe 80% of the time — it behaves like XLOOKUP with a blank if-not-found. Inner silently drops unmatched rows, which is fine when you meant it and a quiet data loss when you did not.

Left Anti is the underrated one. It is not for joining at all; it is a diagnostic. Merge the orders against the customer master with Left Anti and the result is precisely the orders whose customer does not exist in the master — an exception report with no formula, no conditional formatting and no scrolling.

🎯 Scenario: Fixing the casing problem from section 3 properly.

Text.Proper gives you Nordwind Gmbh. A two-column mapping table gives you Nordwind GmbH:

Key              Display
nordwind gmbh    Nordwind GmbH
alpine sports    Alpine Sports
rivera y cia     Rivera y Cia
baltic marine    Baltic Marine

Lowercase and trim the Customer column to build the key, Merge against this table on it, expand the Display column, remove the working columns. Now casing is data you control rather than a guess an algorithm makes, and adding a fifth customer means adding a row to a table — not editing a query.

After any Merge you get a column of nested tables with an expand icon (⇔) in its header. Click it, tick only the columns you actually need, and untick "Use original column name as prefix" unless you want Customers.Display everywhere.

7) Group By, and Where the Result Lands

Transform → Group By is a PivotTable that stays in the pipeline. Group the cleaned orders by Region, aggregate Sum of Line Total:

(Line Total is a Custom Column: = [Qty] * [Unit Price]. Add it after both columns are properly typed, or you are multiplying text.)

Group By has one option worth knowing: an aggregation called All Rows, which keeps the underlying rows in a nested table per group instead of collapsing them. That is how you do "the largest order per customer" — group by customer with All Rows, then add a custom column that reaches into each group's table.

Then you choose where the result goes. Home → Close & Load To offers four destinations, and picking the wrong one is how a workbook ends up 80 MB:

• Table — a sheet full of rows. Right for a final output someone reads.
• PivotTable Report — the query feeds a pivot directly, no intermediate sheet.
• Only Create Connection — the query exists and can be referenced by other queries, but nothing lands on a sheet. This is the correct choice for staging queries and lookup tables. A 400,000-row source that only exists to be merged has no business being written to a worksheet, and Excel's million-row limit means sometimes it cannot be.
• Add this data to the Data Model — into Power Pivot, for relationships and DAX measures across several tables.

Refresh with Data → Refresh All (Ctrl+Alt+F5), or right-click the query in the Queries & Connections pane. In Query Properties you can tick Refresh data when opening the file — worth doing on any report someone else opens, since the single most common Power Query support ticket is a stale table that nobody refreshed.

8) The Six Ways a Query Breaks

Everything above works. Here is what goes wrong in month two.

1. A source column gets renamed. The most common failure by far. Half the steps reference columns by name, so Customer becoming Customer Name upstream produces "The column 'Customer' of the table wasn't found." and the query stops dead. Nothing prevents this, but a Renamed Columns step placed immediately after Source contains the damage: one step to edit instead of nine.

2. The Changed Type step pins column names. When you import, Power Query helpfully adds an automatic Changed Type step listing every column by name. It is the single most brittle step in a typical query. Either delete it and type only the columns you need, or turn the automatic version off entirely: File → Options and settings → Query Options → Data Load → uncheck "Automatically detect column types".

3. Hard-coded file paths. Source = Csv.Document(File.Contents("C:\Users\you\Desktop\feb.csv")) works beautifully until someone else opens the workbook. Put the path in a one-cell Table in the workbook, load it as its own query, and reference that — or use a folder query, which only needs the folder.

4. Formula.Firewall. "Query references other queries or steps, so it may not directly access a data source." This is the privacy-level system refusing to combine two sources it thinks might leak one into the other. The clean fix is to split the query in two — one that fetches, one that transforms. The blunt fix is Query Options → Privacy → Ignore the Privacy Levels, which you should only reach for when you know both sources are yours.

5. Step order matters more than it looks. Filtering before a Merge is fast; filtering after it means joining rows you then throw away. Removing columns early makes everything downstream quicker. And a Replace Values step that runs before a type change operates on text, while the same step after it operates on numbers — same dialog, different behaviour.

6. Nobody refreshes it. The query is right, the output is stale, and the person reading the sheet has no way to tell. Tick Refresh data when opening the file, and put the refresh timestamp on the sheet — a one-row query with = DateTime.LocalNow() loaded to a cell does it, and it is the cheapest piece of trust you can add to a report.

And the honest counterweight: Power Query is not always the answer. For a one-off clean of forty rows you will never see again, opening the editor costs more than fixing it by hand. For anything a person needs to see recalculate as they type, a formula is right and a query is wrong — queries only update on Refresh, never as you edit a cell. Power Query earns its keep exactly when the same shape of data arrives more than once.

Conclusion

Formulas and queries answer different questions. A formula answers what is this number — live, in a cell, recalculating as you type. A query answers how do I turn this file into that table — once, in a recorded list of steps that runs again whenever the file changes.

Most workbooks that hurt to maintain are workbooks where formulas were made to answer the second question. The forty nested SUBSTITUTE calls, the helper columns that exist only to parse a date, the sheet called Paste raw data here — all of it is a pipeline written in the wrong medium.

If you build one query this week, build the boring one: the monthly import you already do by hand. Point it at a folder, do your usual clean by clicking, load it to a table, and tick refresh-on-open. Next month you will find out what the tool is actually for, which is not doing something clever — it is not doing the same thing twice.

Want to practise the formula side of the same problems? Several exercises in the app are built on exactly these shapes — numbers stored as text, a lookup against a reference table, and a conditional total over a column that later gains a row.