HomepageJoin Waitlist
Tutorials

How to Use Excel Power Query for Data Cleaning: Complete Guide

Learn how to use Power Query to clean and transform Excel data. Discover powerful data transformation techniques that automate repetitive cleaning tasks.

RowTidy Team
Dec 2, 2025
13 min read
Excel, Power Query, Data Transformation, Automation, Data Cleaning

How to Use Excel Power Query for Data Cleaning: Complete Guide

Power Query is Excel's most powerful data cleaning tool.
It can automate repetitive cleaning tasks, handle millions of rows, and create reusable data transformation workflows.

Instead of manually cleaning data every time, Power Query lets you:

  • Clean once, reuse forever
  • Handle large datasets Excel struggles with
  • Combine multiple data sources
  • Automate complex transformations

This guide shows you how to use Power Query for data cleaning.

🚨 What is Power Query?

Power Query (called "Get & Transform Data" in newer Excel) is a data connection technology that lets you:

  • Import data from various sources
  • Transform and clean data
  • Combine multiple data sources
  • Create reusable data cleaning workflows

Available in:

  • Excel 2016+ (Windows)
  • Excel for Microsoft 365
  • Excel 2019+ (Mac)

Benefits:

  • Handles millions of rows
  • Reusable queries
  • No formulas needed
  • Visual interface
  • Automatic refresh

🛠 Getting Started with Power Query

Enable Power Query

Check if Available:

  1. Open Excel
  2. Look for Data tab
  3. Check for Get Data or From Other Sources

If Not Available:

  • Update to Excel 2016+ or Office 365
  • Or install Power Query add-in (Excel 2010-2013)

Load Data into Power Query

Method 1: From Excel File

  1. Data > Get Data > From File > From Workbook
  2. Select Excel file
  3. Choose sheet/table
  4. Click Transform Data

Method 2: From CSV

  1. Data > Get Data > From File > From Text/CSV
  2. Select CSV file
  3. Preview data
  4. Click Transform Data

Method 3: From Folder

  1. Data > Get Data > From File > From Folder
  2. Select folder
  3. Combine files if needed
  4. Click Transform Data

🛠 Common Data Cleaning Tasks

Task 1: Remove Duplicates

Steps:

  1. Select column(s) with duplicates
  2. Home > Remove Duplicates
  3. Click OK

Result: Duplicates removed automatically

Keep First vs Last:

  • Power Query keeps first occurrence
  • Sort before removing if you want to keep specific record

Task 2: Filter Data

Steps:

  1. Click filter arrow on column
  2. Choose filter criteria:
    • Text Filters: Contains, equals, starts with
    • Number Filters: Greater than, less than, between
    • Date Filters: Before, after, between dates
  3. Click OK

Examples:

  • Remove blanks: Filter > Remove empty
  • Keep only active: Text filter > equals "Active"
  • Recent dates: Date filter > after 2024-01-01

Task 3: Remove Rows

Remove Top Rows:

  1. Home > Remove Rows > Remove Top Rows
  2. Enter number of rows
  3. Click OK

Remove Bottom Rows:

  1. Home > Remove Rows > Remove Bottom Rows
  2. Enter number of rows

Remove Alternate Rows:

  1. Home > Remove Rows > Remove Alternate Rows
  2. Set first row to remove, number of rows, rows to keep

Remove Blank Rows:

  1. Home > Remove Rows > Remove Blank Rows

Task 4: Split Columns

Split by Delimiter:

  1. Select column
  2. Transform > Split Column > By Delimiter
  3. Choose delimiter (comma, space, custom)
  4. Choose split position (left, right, each occurrence)
  5. Click OK

Example: Split "John, Smith"

  • Delimiter: Comma
  • Result: "John" | "Smith"

Split by Number of Characters:

  1. Transform > Split Column > By Number of Characters
  2. Enter character count
  3. Choose position

Split by Positions:

  1. Transform > Split Column > By Positions
  2. Enter positions (e.g., 5, 10)

Task 5: Merge Columns

Combine Columns:

  1. Select multiple columns (Ctrl+Click)
  2. Transform > Merge Columns
  3. Choose separator (space, comma, custom)
  4. Enter new column name
  5. Click OK

Example: Merge First + Last Name

  • Columns: FirstName, LastName
  • Separator: Space
  • Result: "John Smith"

Task 6: Change Data Types

Convert Types:

  1. Click data type icon on column header
  2. Choose type:
    • Whole Number: Integers
    • Decimal Number: Decimals
    • Text: Text strings
    • Date: Dates
    • Time: Time values
    • Date/Time: Both

Fix Detection Errors:

  • Power Query auto-detects types
  • May detect incorrectly
  • Manually set correct type

Handle Errors:

  • Transform > Replace Errors: Replace with default value
  • Transform > Remove Errors: Remove rows with errors

Task 7: Replace Values

Simple Replace:

  1. Select column
  2. Transform > Replace Values
  3. Enter value to find
  4. Enter replacement value
  5. Click OK

Advanced Replace:

  • Replace nulls: Find null, replace with 0 or ""
  • Replace errors: Use Replace Errors feature
  • Multiple replacements: Apply multiple replace steps

Task 8: Add Custom Columns

Create Calculated Column:

  1. Add Column > Custom Column
  2. Enter column name
  3. Enter formula (M language)
  4. Click OK

Example Formulas:

[Sales] * 1.1  ' Add 10%
[FirstName] & " " & [LastName]  ' Concatenate
if [Age] > 65 then "Senior" else "Regular"  ' Conditional

Common Calculations:

  • Percentage: [Part] / [Total] * 100
  • Difference: [New] - [Old]
  • Concatenate: [Col1] & " " & [Col2]

Task 9: Pivot and Unpivot

Unpivot Columns (Wide to Long):

  1. Select columns to unpivot
  2. Transform > Unpivot Columns
  3. Creates Attribute and Value columns

Pivot Columns (Long to Wide):

  1. Select Attribute and Value columns
  2. Transform > Pivot Column
  3. Choose values column
  4. Click OK

Use Cases:

  • Unpivot: Monthly columns to rows
  • Pivot: Categories to columns

Task 10: Group and Aggregate

Group Data:

  1. Select column to group by
  2. Transform > Group By
  3. Choose aggregation:
    • Sum, Count, Average, Min, Max
  4. Add multiple aggregations
  5. Click OK

Example:

  • Group by: Region
  • Sum: Sales
  • Count: Orders
  • Average: Price

🛠 Advanced Power Query Techniques

Combine Multiple Files

From Folder:

  1. Data > Get Data > From File > From Folder
  2. Select folder with files
  3. Click Combine > Combine & Transform Data
  4. Choose sample file
  5. Power Query combines all files

Benefits:

  • Combines hundreds of files
  • Handles different structures
  • Applies same transformations

Parameterize Queries

Create Parameters:

  1. Home > Manage Parameters > New Parameter
  2. Enter name and default value
  3. Use in queries: #"Parameter Name"

Use Cases:

  • File paths
  • Date ranges
  • Filter values

Example:

  • Parameter: StartDate
  • Use in filter: [Date] >= StartDate

Error Handling

Handle Errors Gracefully:

  1. Transform > Replace Errors
  2. Enter replacement value
  3. Or use Remove Errors

Try Otherwise:

try [Column] otherwise "Error"

Check for Errors:

  • Add conditional column
  • Check if error exists
  • Handle accordingly

🤖 Power Query vs Manual Cleaning

Comparison

Task Manual (Formulas) Power Query
Remove duplicates Data > Remove Duplicates Transform > Remove Duplicates
Split columns Text to Columns Split Column
Combine files Copy-paste From Folder
Handle large data Slow/Impossible Fast (millions of rows)
Reusability Manual each time Save and reuse
Automation Manual refresh Auto-refresh

Power Query Wins For:

  • Large datasets
  • Repetitive tasks
  • Multiple files
  • Complex transformations
  • Automation needs

🤖 Advanced: AI-Powered Data Cleaning

While Power Query is powerful, RowTidy can complement it:

  1. Pre-Clean Data

    • Clean before Power Query
    • Standardize formats
    • Fix common issues

  2. Handle Complex Patterns

    • AI detects patterns
    • Suggests transformations
    • Handles edge cases

  3. Combine with Power Query

    • Use RowTidy for initial cleaning
    • Use Power Query for transformations
    • Best of both worlds

Benefits:

  • ✅ Faster setup with pre-cleaned data
  • ✅ Better results with AI assistance
  • ✅ Handles complexity Power Query struggles with

📊 Real Example: Cleaning Sales Data

Before (Messy Data):

  • Multiple files from different regions
  • Inconsistent date formats
  • Mixed product names
  • Duplicate entries
  • Missing values

Power Query Steps:

  1. From Folder: Combine all regional files
  2. Change Type: Fix date and number columns
  3. Remove Duplicates: Based on Order ID
  4. Split Column: Separate product code and name
  5. Replace Values: Standardize product names
  6. Fill Down: Handle missing regions
  7. Add Column: Calculate totals
  8. Filter: Remove test orders

After (Clean Data):

  • Single combined file
  • Consistent formats
  • No duplicates
  • Standardized names
  • Complete data
  • Ready for analysis

Time Saved: 4 hours → 15 minutes

✅ Power Query Best Practices

Query Organization:

  • Name queries clearly
  • Group related queries
  • Document transformations
  • Use comments in M code

Performance:

  • Filter early (reduce data size)
  • Remove unnecessary columns
  • Use appropriate data types
  • Avoid complex calculations in queries

Maintenance:

  • Test queries with sample data
  • Handle errors gracefully
  • Document parameter values
  • Version control queries

🔗 Related Guides

  1. Excel Data Cleaning Guide - Comprehensive cleaning
  2. Automate Excel Cleanup - Automation techniques
  3. How to Merge Excel Files - File combining
  4. Excel Automation for Inventory - Real-world automation
  5. Complete Excel Guide - Full Excel skills

📌 Conclusion

Power Query is a game-changer for Excel data cleaning. The techniques in this guide will help you:

  • Clean data efficiently
  • Handle large datasets
  • Automate repetitive tasks
  • Create reusable workflows

For basic cleaning: Use Power Query's built-in features
For complex patterns: Combine with RowTidy
For automation: Use parameters and refresh

Remember: Power Query is about doing it once and reusing forever. Invest time in building good queries, and you'll save hours on future cleaning tasks.

✍️ Ready to automate your data cleaning?

👉 Try RowTidy today to complement Power Query with AI-powered cleaning. Get started with a free trial and see how AI can enhance your Power Query workflows.

This guide is part of our comprehensive series on Excel data management. Check out our other tutorials on data cleaning, automation, and Power Query for complete data solutions.