Excel Power Query: Automate Data Cleaning Like a Pro

What is Power Query?

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool. It lets you connect to data sources, clean and reshape data, and load it into worksheets — all without formulas or VBA.

Opening Power Query

1. Go to Data tab
2. Click Get Data (or From Table/Range for existing data)
3. The Power Query Editor opens

Basic Transformations

Remove Columns

1. Select columns you don't need
2. Right-click → Remove Columns

Filter Rows

1. Click the dropdown arrow on a column header
2. Uncheck values you want to exclude

Change Data Types

1. Click the icon next to the column name
2. Select the correct type (Text, Number, Date, etc.)

Rename Columns

1. Double-click the column header
2. Type the new name

Merging Tables (Like VLOOKUP on Steroids)

Merge Queries

1. Home → Merge Queries
2. Select the two tables
3. Choose the matching columns
4. Select join type (Left, Inner, Full)
5. Expand the merged column to show fields

Example: Match Orders with Customers

Table A (Orders)          Table B (Customers)
┌──────────┬─────────┐   ┌──────────┬──────────┐
│ OrderID  │ CustID  │   │ CustID   │ Name     │
├──────────┼─────────┤   ├──────────┼──────────┤
│ 1001     │ C01     │   │ C01      │ Alice    │
│ 1002     │ C03     │   │ C02      │ Bob      │
│ 1003     │ C01     │   │ C03      │ Charlie  │
└──────────┴─────────┘   └──────────┴──────────┘

After Merge (Left Join on CustID):
┌──────────┬─────────┬──────────┐
│ OrderID  │ CustID  │ Name     │
├──────────┼─────────┼──────────┤
│ 1001     │ C01     │ Alice    │
│ 1002     │ C03     │ Charlie  │
│ 1003     │ C01     │ Alice    │
└──────────┴─────────┴──────────┘

Appending Tables (Stack Rows)

1. Home → Append Queries
2. Select tables to combine
3. Result: all rows stacked vertically

Use this to combine monthly reports, daily logs, etc.

Common Data Cleaning Recipes

Remove Duplicates

1. Select the column(s)
2. Home → Remove Rows → Remove Duplicates

Split Columns

1. Right-click column → Split Column
2. Choose delimiter (comma, space, etc.)
3. Or split by number of characters

Fill Down Nulls

1. Select the column
2. Transform → Fill → Down

This replaces null values with the last non-null value above.

Unpivot Columns

Convert wide data to tall format:

Before (Wide):
┌─────────┬──────┬──────┬──────┐
│ Product │ Q1   │ Q2   │ Q3   │
├─────────┼──────┼──────┼──────┤
│ Widget  │ 100  │ 150  │ 200  │
└─────────┴──────┴──────┴──────┘

After Unpivot:
┌─────────┬────────┬───────┐
│ Product │ Quarter│ Sales │
├─────────┼────────┼───────┤
│ Widget  │ Q1     │ 100   │
│ Widget  │ Q2     │ 150   │
│ Widget  │ Q3     │ 200   │
└─────────┴────────┴───────┘

M Language (Advanced)

Power Query uses M language behind the scenes. You can edit it directly in the formula bar or Advanced Editor:

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Amount] > 100),
    SortedRows = Table.Sort(FilteredRows, {{"Amount", Order.Descending}}),
    AddedTotal = Table.AddColumn(SortedRows, "Total", each [Amount] * 1.1)
in
    AddedTotal

Refreshing Data

After setting up your query:

1. Data → Refresh All (or Ctrl+Alt+F5)
2. All transformations re-apply automatically
3. New data from the source is processed

Auto-Refresh on File Open

1. Data → Queries & Connections
2. Right-click query → Properties
3. Check Refresh data when opening the file

Connecting to External Sources

CSV File

Data → Get Data → From File → From Text/CSV

Database

Data → Get Data → From Database → From SQL Server Database

Web API

Data → Get Data → From Other Sources → From Web
Enter URL: https://api.example.com/data

Folder (Combine Multiple Files)

Data → Get Data → From File → From Folder
Select folder → Power Query detects file structure

Tips

1. Name your queries — descriptive names make maintenance easier
2. Document steps — add comments in Advanced Editor
3. Use parameters for dynamic values (date ranges, file paths)
4. Close & Load To → only create connection for intermediate queries
5. Parameters let users input values at refresh time