Excel Power Query: Cleaning Data Without Code

Many analysts spend hours every month on the same cleaning: pasting exports together, trimming spaces, deleting cancelled orders, fixing country names, looking up categories. Done by hand, it is slow, error-prone and impossible to audit. Power Query, built into Excel on the Data tab under Get & Transform, turns that routine into a recorded sequence of steps that runs again with one click. Microsoft describes it as a data transformation and preparation engine that saves the steps as a repeatable query you can refresh (Microsoft Learn: what is Power Query).

The example below follows a typical case: a monthly sales export from an online shop, with the problems real exports have, such as cancelled orders, stray spaces, inconsistent country codes and duplicate lines. Data cleaning, step by step shows the same problems on real data and explains why each matters.

Step 1: Combine a folder of monthly files

Save each month's export in one folder, then choose Data, Get Data, From File, From Folder, and Combine & Transform. Power Query stacks every file into one table. Next month, drop the new file into the folder and refresh: no copying and pasting, and no risk of pasting a month twice.

Step 2: Check and set data types

Power Query guesses types, and the guesses need checking. Product codes such as 85123A must be text, not numbers; invoice numbers that start with a letter must stay text; dates must be dates in the right locale; quantities and prices must be numbers. Set each column's type from the column header. Wrong types are the most common cause of totals that do not add up later.

Step 3: Clean text

Select text columns and use Transform, Format, Trim, then Clean, to remove leading and trailing spaces and non-printable characters. In M, the language behind Power Query, the generated step looks like this:

= Table.TransformColumns(#"Changed Type", {{"Description", Text.Trim, type text}, {"Country", Text.Trim, type text}})

Consistent capitalisation matters too, for reasons that appear in step 5.

Step 4: Filter out rows that are not sales

In the example export, cancelled orders have invoice numbers starting with "C". Use the filter arrow on the column, Text Filters, Does Not Begin With, and enter C:

= Table.SelectRows(#"Trimmed Text", each not Text.StartsWith([InvoiceNo], "C"))

Filter out postage, fee and adjustment lines the same way, and keep a note of what each filter removes. Filtering in the query rather than deleting rows in the sheet means the rule is applied identically every month.

Step 5: Standardise values and remove duplicates

Replace inconsistent codes, such as "EIRE" with "Ireland", using Transform, Replace Values:

= Table.ReplaceValue(#"Filtered Rows", "EIRE", "Ireland", Replacer.ReplaceValue, {"Country"})

Then remove duplicate rows with Home, Remove Rows, Remove Duplicates. One trap: Power Query compares text case-sensitively, so "Red Mug" and "RED MUG" count as different values. Microsoft's documentation suggests applying an uppercase or lowercase transform first if that is not what you want (Microsoft Learn: working with duplicates). Decide which columns define a duplicate: removing duplicates across all columns and across only an order number give very different results.

Step 6: Merge in lookup data

To add a product category from a separate product list, use Home, Merge Queries, choose the product table, select the matching columns in both, and pick the join kind. A left outer join keeps every sales row and adds the category where a match exists (Microsoft Learn: merge queries). Then expand the merged column to bring in only the fields you need. Afterwards, filter the new column for blanks: rows without a match reveal products missing from the lookup table, a problem that a formula-based lookup would hide behind #N/A or an IFERROR.

Step 7: Unpivot wide tables

Budgets and targets often arrive in a wide layout, one column per month. Analysis tools need them long: one row per product and month. Select the identifier columns, then Transform, Unpivot Other Columns. The month columns become rows of attribute and value pairs (Microsoft Learn: unpivot columns), which can then be joined to actual sales by product and month.

Step 8: Load, then refresh

Close & Load sends the result to a worksheet table or to the Excel data model for pivot tables. Every step you took is listed in the Applied Steps pane, where you can rename, reorder, edit or delete it (Microsoft Learn: applied steps). That list is your cleaning log: anyone can open the query and see exactly what was done. Next month, Data, Refresh All repeats the entire process on the new files.

Good practice with Power Query

• Rename steps to say what they do, such as "Removed cancellations", so the Applied Steps list reads like documentation.
• Filter early to reduce the data each later step processes.
• Keep lookup tables in their own queries, so they are maintained in one place.
• Add a check query that counts rows and totals revenue after each refresh, and compare it with the source system.
• Avoid manual edits in the loaded table; they are overwritten on refresh.

Power Query or formulas?

Use Power Query for preparation that repeats: combining, cleaning, reshaping and merging. Use formulas for calculations on the prepared table, such as totals, ratios and flags; Excel formulas for data analysis covers them. The same Power Query engine is also built into Power BI, so the skills transfer directly.

Data cleaning in Excel, Power Query Editor in Power BI, and data transformation are part of the Data Analytics course.

Sources

• What is Power Query? (Microsoft Learn) — Microsoft Learn
• Applied Steps (Microsoft Learn) — Microsoft Learn
• Working with duplicate values (Microsoft Learn) — Microsoft Learn
• Merge queries overview (Microsoft Learn) — Microsoft Learn
• Unpivot columns (Microsoft Learn) — Microsoft Learn