How to Clean Data Using Power Query in Excel
Data quality is critical to accurate analysis. When data comes from various sources (manual entry, OCR scans, other systems), it inevitably gets messy. Power Query transforms Excel from a basic calculator into an industrial-strength data cleaning powerhouse.
Table of Contents
Step 1: Loading Data Into the Editor
The Basic Steps
- Select Your Range: Select your raw data within Excel.
- Load to Power Query: Go to Data > From Table/Range. This opens the powerful “Power Query Editor” window where all cleaning takes place.
Step 2: Core Data Cleaning Tasks (The Toolkit)
1. Removing Unwanted Rows/Columns
If you have unnecessary footers, junk rows or irrelevant columns, this is the first fix.
- Removing Columns: Simply select the column header(s) and right-click > Remove.
- Filtering Rows: Use the filter arrow on the column header to exclude blank rows or specific categories.
2. Standardizing Text Data
Raw text is messy (leading/trailing spaces, inconsistent capitalization). These tools fix it:
- Trim Values: Select the column and find Transform > Format > Trim. This removes all extra whitespace.
- Capitalization Case: Use Transform > Format > Capitalize Each Word (or Title Case) to ensure consistency.
3. Correcting Data Types
If a column is mixed (some numbers, some text), Power Query will warn you and often default it to Text type. This prevents calculations.
- Select the column and use the Data Type selector in the top bar to explicitly change it to Number, Date, or Decimal Number.
- If conversion fails, double-check for hidden text characters that are preventing the number format.
4. Splitting Data (Text Manipulation)
When a single field contains multiple pieces of information separated by a character (like a comma or space), you need to split it:
- Select the column containing the combined data.
- Go to Transform > Split Column.
- Choose your delimiter (Comma, Space, Pipe “|” etc.) and click OK. Power Query will separate the data into new columns based on that character.
Advanced Cleaning Scenarios (The Pro Moves)
Finding and Replacing Values
This is used to correct systemic errors across the dataset.
- Use Transform > Replace Values. You can search for a mistake (e.g., “Calif”) and replace it with the standardized version (“California”).
- You can even use regex replacement to clean complex patterns of text or numbers.
Handling Missing Values
Missing data (NULL) must be addressed:
- Replace Nulls: Right-click the column header, select Fill, and choose a method like ‘Down’ or ‘Up’ if you can logically infer the missing value.
- Ignore/Filter Out: If the null simply means no data, you can filter those rows out of your dataset.
Creating New Calculated Columns
Often, cleaning isn’t just correcting; it’s creating new value fields. This uses a formula within Power Query.
- Go to Add Column > Custom Column.
- Write your formula (e.g., [Price] * [Quantity]). The resulting column is the clean, calculated metric you need for analysis.
Finalizing and Auditing Your Clean Data
Never trust a query without an audit. Follow these best practices:
- Always Check “Applied Steps”: Review the Applied Steps pane on the right. It shows you exactly what Power Query did (e.g., Trimmed values, Changed data type). If you get an unexpected result, you can jump back to a previous step and correct it.
- Test with Sample Data: Before cleaning your entire dataset, copy a small section of 10-20 rows. Test all complex transformations (like splitting or formulas) on the sample first.
- Close & Load To… When finished, use Data > Close & Load To… and specify whether you want to load it as a connection only or as an actual table in Excel.
