Power Query in Excel: Clean Messy Data the Easy Way

Every month the same thing happens. Someone sends you an export full of extra spaces, merged headings, dates in three formats, blank rows and duplicate entries. You spend an hour cleaning it by hand, build your report, and then do it all again next month. Power Query in Excel exists to end that cycle.

Power Query lets you clean and reshape data with a few clicks, records every step, and repeats those steps automatically when new data arrives. This tutorial walks you through it from the very first click, with a worked example you can follow.

Quick answer: Power Query is Excel’s built-in tool for importing and cleaning data. Go to Data > Get Data (or From Table/Range), clean the data in the Power Query Editor using buttons such as Trim, Split Column, Data Type, Remove Duplicates, Fill Down and Unpivot, then choose Close & Load. Next time, replace the source data and click Refresh; every step runs again automatically.

What is Power Query?

Power Query is a data preparation tool built into modern versions of Excel, and also into Power BI. Microsoft’s Power Query documentation describes it as a data transformation and preparation engine.

In practice, it does three jobs:

1. Connects to data: Excel tables, CSV and text files, folders of files, databases and more.
2. Transforms the data: cleaning, splitting, filtering, reshaping and combining.
3. Loads the result into a worksheet table, a PivotTable or Excel’s data model.

The key idea is that Power Query doesn’t change your source. It builds a recipe of steps, shown in the Applied Steps pane, and applies that recipe each time you refresh.

Why use Power Query instead of manual cleaning?

Manual cleaning	Power Query
Repeat every step by hand each time	Record steps once, refresh to repeat
Easy to miss a row or make a typo	Same steps applied the same way every time
Hard to see what was changed	Every step listed and editable
Formulas can slow large sheets	Cleaning happens before data reaches the sheet
Combining files means copy and paste	Append, merge or load a whole folder

If you clean the same kind of report more than once, Power Query will pay back the time you spend learning it.

Opening the Power Query Editor

In Excel for Windows (Microsoft 365 and recent versions):

1. Click any cell in your data.
2. Go to the Data tab.
3. In the Get & Transform Data group, choose From Table/Range. If your data isn’t already a table, Excel will ask to create one; tick “My table has headers” if it does.

For a CSV or another workbook, choose Get Data > From File instead.

The Power Query Editor opens in a new window, showing a preview of your data, the ribbon of tools at the top and the Applied Steps pane on the right.

Worked example: cleaning a messy sales export

Imagine a monthly export with these problems: extra spaces around names, a “Customer – City” column that should be two columns, dates stored as text, duplicate orders, region names that only appear on the first row of each group, and one column per month instead of a single date column.

Here’s how to fix each one.

Step 1: Remove columns you don’t need

Select the columns you want to keep (Ctrl+click), then choose Home > Remove Columns > Remove Other Columns. This keeps the query fast and focused.

Step 2: Trim and clean text

Select the text columns, then choose Transform > Format > Trim to remove extra spaces at the start and end. Use Clean as well to strip out hidden non-printing characters that often come from exports.

Step 3: Split a combined column

Select “Customer – City”, then Home > Split Column > By Delimiter. Choose the dash (or type a custom delimiter) and split at the left-most delimiter. Rename the new columns by double-clicking their headers.

Step 4: Set the right data types

Click the small icon at the left of each column header and choose the correct type: Text, Whole Number, Decimal Number or Date. Getting types right matters, because sums, filters and dates all depend on it. If dates appear in a format your system doesn’t expect, use Using Locale from the same menu to tell Power Query how to read them.

Step 5: Fill down missing values

For the region column that only shows the value on the first row of each group, select it and choose Transform > Fill > Down. Each blank cell takes the value above it.

Step 6: Remove blank rows and duplicates

Use Home > Remove Rows > Remove Blank Rows. Then select the column or columns that identify a unique order, such as Order ID, and choose Remove Rows > Remove Duplicates.

Be careful here: choose the identifying columns deliberately. Removing duplicates across the wrong columns can delete real data.

Step 7: Unpivot month columns

If your data has a column for each month, select the columns that describe each row (such as Product and Region), then choose Transform > Unpivot Columns > Unpivot Other Columns. You’ll get two new columns, Attribute and Value, which you can rename to Month and Sales. This “long” shape is far easier to analyse with a PivotTable.

Step 8: Close & Load

Choose Home > Close & Load to send the clean table to a new worksheet. Or use Close & Load To to load it straight into a PivotTable or the data model.

Refreshing next month

This is where Power Query earns its place. When next month’s export arrives:

1. Replace the old source file with the new one using the same name and location, or paste the new data into the same source table.
2. In Excel, go to Data > Refresh All.

Every step runs again on the new data. A job that took an hour now takes moments, as long as the new file has the same structure.

Combining data from several sources

Append: stack tables

If you have January, February and March tables with the same columns, use Home > Append Queries to stack them into one.

Merge: join tables

To bring in information from another table, such as adding product category from a product list, use Home > Merge Queries and match on a shared column like Product ID. This works like a lookup, and if you know XLOOKUP or INDEX/MATCH, the idea will feel familiar.

From Folder: load many files at once

Choose Get Data > From File > From Folder, pick a folder of similarly structured files, and choose Combine & Transform. Power Query applies the same steps to every file. Drop a new file into the folder and refresh to include it.

Common Power Query mistakes

• Skipping data types. Text that looks like numbers won’t add up correctly.
• Renaming source columns. If a column name changes in the source file, steps that refer to it can break. Keep source headers consistent.
• Removing duplicates too broadly. Always choose the right identifying columns.
• Not checking row counts. Compare the row count before and after key steps so you know nothing important was dropped.
• Editing the output table by hand. Manual edits to a loaded table are lost on refresh. Make changes in the query instead.

A broader data cleaning checklist helps you decide what to clean and why, before you open the editor.

A note on sensitive data

Power Query works on your own computer, but the files it reads often contain customer or staff details. Keep source files in approved locations, remove columns you don’t need, and be careful when sharing workbooks that still hold the full data. If you later explore the cleaned data with AI tools, remove personal details first.

How Omebly can help

If you’d like guided, hands-on practice with Excel skills like this, Omebly Academy’s Advanced Excel course is a good next step. Classes are taught live by Nazmul Hussain’s team; Nazmul Hussain is Omebly’s Managing Director and Technical Head, with 10+ years of experience.

If you want to take cleaned data further into analysis, look at the Data Analysis course. And when you need to summarise a long PDF report before you analyse it, our free AI summarizer can give you a quick overview to check against the original. You can compare every option on the Omebly Academy training page.

Ready to stop cleaning the same report by hand? See the Advanced Excel course.