Power Query cleans data in Excel by recording each fix as a step, so you build the cleanup once and rerun it on every new export. You load the messy table, set data types, trim text, deal with errors, split combined columns, merge in lookup tables, and click Refresh next month. This guide walks through each of those moves in the order you should do them, with the traps that cost people an afternoon.
Every analyst has done the manual version: delete the first six rows of a report, retype a header, run Find and Replace, paste values, and repeat the whole routine when the next export arrives. It works once. The second time, you forget a step, and the third time, someone changes the file layout.
Power Query (called Get & Transform on the Data tab) fixes that by storing the process rather than the result. According to Microsoft's best practices guidance, the steps you take are recorded in the Query Settings pane as Applied Steps. That has two practical consequences:
Before you change anything, look. In the Power Query Editor, open the View tab and switch on the profiling tools. Microsoft lists three: Column Quality, Column Distribution and Column Profile. Per Microsoft, Column Quality shows the share of valid, error and empty values, Column Distribution visualizes how often values repeat, and Column Profile gives detailed statistics for the selected column.
Here is what to look for:
There is no single correct order, but this one avoids most rework.
Reports exported from accounting or ERP systems often carry titles, dates and blank lines above the real headers. Use Remove Rows > Remove Top Rows, then Use First Row as Headers. These are among the most common cleaning operations, and they are the ones that make later steps stable. Do the same at the bottom for grand-total rows you do not want in a PivotTable.
If you only need 2026 transactions, filter now. Microsoft recommends filtering data as early as possible so later transformations process fewer rows. For database sources that support query folding, the filter can be pushed back to the source. For CSV and Excel files, folding does not apply, but a smaller table still means faster refreshes and fewer places for errors to appear.
Power Query guesses types when it first loads a table. Check the guesses. Dates that arrive as text, ZIP codes that lost their leading zeros, and IDs read as numbers are classic problems. For ZIP codes and account numbers, set the type to Text. For dates from a different regional format, use Change Type > Using Locale so "03/04/2026" is read the way the source system meant it.
Text columns collect invisible damage: trailing spaces, double spaces, mixed case, non-printing characters. Select the columns, then Transform > Format:
Use Replace Values for known substitutions, such as turning "N/A" into null, or "Calif." into "California". Fill Down handles the grouped-report layout where a region name appears once and the blanks below belong to it.
Deduplicate after trimming and case standardization. Otherwise "Acme Corp" and "Acme Corp " count as two customers, and you keep both. Remember that Remove Duplicates compares the selected columns only, and it is case-sensitive, which is one more reason to standardize text first.
An error in Power Query is not the same as an error in a worksheet. It shows up in a single cell, and the rest of the row is fine. Microsoft's error-handling documentation describes three ways to treat them: remove the rows that contain errors, replace the errors with a fixed value, or keep the rows so the errors serve as an audit trail.
To see why a cell failed, click the white space inside the cell. A pane under the data preview shows the details. That one habit saves a lot of guessing.
| Situation | Best move | Why |
| Errors in a column you do not use | Remove the column, not the rows | You keep every record |
| A few bad rows in a large export, and totals must reconcile | Keep errors, then investigate | Removing them silently changes totals |
| Missing values should read as zero | Replace Errors with 0 | Downstream sums stay clean |
| Different fallback depending on context | Use a try ... otherwise expression | Conditional handling per column |
The documentation also covers the "column of the table wasn't found" error. It happens when a step refers to a column name that no longer exists, for example because the source file changed a header from "Amount" to "Amt". The fix is to find the step that references the old name and update it, or to build your cleanup so it does not depend on fragile names. Position-based steps and promoted headers help, but there is no magic here. If the source layout changes, a human has to look.
Combined fields are everywhere: "Smith, Jane", "Austin, TX 78701", "SKU-1042-RED". Select the column and choose Split Column on the Home or Transform tab.
Microsoft's documentation says you can split text by delimiter, by number of characters, by position, or by case change such as lowercase to uppercase. Delimiters include colon, comma, equals sign, semicolon, space and tab, and you choose whether to split at the left-most delimiter, the right-most, or at each occurrence.The choice among those three matters more than people expect:
If you prefer worksheet formulas, the same logic exists in LEFT, MID and RIGHT. Our guide to Excel text functions shows how they work, and it helps to know both approaches, because Power Query is not always the right tool for a quick one-off fix.
Merging is where Power Query replaces the VLOOKUP-and-drag routine. Microsoft describes a merge as creating a new query from two existing ones, with a column in the primary table that holds a link to the related table. You then expand that column and choose which fields to bring in.
The steps are short:
| Join kind | Keeps | Typical use |
| Left Outer | Every row of the first table, matches from the second | Add product names to a sales list |
| Inner | Only rows that match in both | Orders that have a valid customer |
| Left Anti | Rows in the first table with no match | Find orders whose customer is missing from the master list |
| Full Outer | Everything from both | Reconcile two lists |
If matches fail unexpectedly, the cause is almost always hidden spaces or case differences. Go back and trim the key columns in both queries, then merge again.
The bigger payoff comes when you point Power Query at a folder. Microsoft's file-combining documentation explains that Combine Files creates a Transform Sample File query. Whatever cleaning you apply to that sample runs on every file in the folder before they are stacked together.
That is how a monthly routine becomes a one-click job: drop 12 CSV exports in a folder, clean the sample once, and refresh. The approach can be brittle if file structures change, so keep a consistent header row and check Column Quality after each refresh.
Two concepts trip up people who move from small files to larger sources.
Query folding. Per Microsoft, folding translates your Power Query steps into native queries the data source runs. Steps that cannot fold run inside the Power Query engine, which is slower on large data. Many common sources, including CSV and Excel files, do not support folding, so for those the advice is to filter early and keep the step list lean. Privacy levels. When you combine data from several sources, Power Query may ask you to set each as Private, Organizational or Public. Microsoft's privacy levels page explains that restrictive isolation blocks data from moving between sources, which can reduce performance. It is tempting to switch on the setting that ignores privacy levels. Microsoft's security guidance recommends configuring levels for each data source instead. If your workbook mixes a customer list with a public price feed, set the customer file to Private and leave it that way.We build LogicExcel around one belief: people learn Excel by doing, not by watching. That applies to Power Query as much as to formulas. The learners we see struggle with data cleaning are rarely missing a button. They are missing the instinct for what "clean" means: which columns are keys, which values should be text, where a stray space will break a match.
That instinct comes from formulas first. If you have trimmed a column with TRIM, split a code with LEFT and MID, and watched a lookup fail on a hidden space, then you know exactly what to do in the Power Query ribbon, and you can read the M code it writes for you. If you jump straight to the ribbon, you click buttons and hope.
Our advice for anyone building this skill:
> — Thomas Houza
Power Query sits on top of ideas you can drill without installing anything. On LogicExcel you can work through 95 free Excel exercises in a real spreadsheet-like environment, get instant feedback, and see an explanation of every mistake. There is no account and no payment. If you want a short route into the topic, the Power Query lesson in Five Wins shows how to clean a table once and reuse it. For sample data to practice on, see our free practice datasets.
Once the basics feel natural, two directions pay off. One is automation: our piece on using Excel to automate data analysis tasks shows how cleaning fits into a wider reporting routine. The other is finance work, where messy exports are the norm. The Excel for Accounting course covers month-end reporting, and 5 Days to Better Accounting Spreadsheets is a short free start.
Select the column, then use Remove Rows > Remove Errors to drop the rows, or Replace Errors to put a fixed value in their place. Click inside an error cell first to read the details in the pane under the preview. Microsoft's guidance also lets you keep errors as an audit tool.
Select the column and choose Split Column on the Home or Transform tab, then pick By Delimiter, By Number of Characters or By Positions. For delimiters you can split at the left-most, right-most or each occurrence. Check the result in Applied Steps and change the data types of the new columns.
Load both tables as queries, open the Power Query Editor, and choose Merge Queries on the Home tab. Select the matching column in each table, pick a join kind such as Left Outer, then expand the new column to bring in the fields you want. Trim and type-match both key columns first.
No. Your source stays as it was, and Power Query records the cleaning as Applied Steps that run on refresh. You can edit or delete any step later. Results appear in a new table or connection, not over your original range.
Guides people read next.
Unlock your Excel potential with the best excel sample data for practice. Discover free datasets across various industries to enhance your skills.
10 min
Master formulas de porcentaje en excel with this step-by-step guide. Simplify calculations and boost your efficiency. Start learning today!
8 minMaster How can I use excel to automate data analysis tasks?: syntax, examples, and step-by-step guidance with free interactive practice from LogicExcel.
9 min