Power Query: Clean Data 10x Faster

Power Query is Excel's built-in data transformation tool. It automates tedious cleaning tasks and remembers your steps for reuse.

How to Access Power Query

Excel: Data tab → Get Data → From File/Table → Transform Data

Or: Data tab → Get & Transform Data section

Common Tasks Made Easy

1. Remove Duplicates

Instead of manually filtering:

• Right-click column → Remove Duplicates
• Done in one click, repeatable on refresh

2. Split Columns

Data: "John Smith" → Split into "First Name" and "Last Name"

Power Query:
- Select column
- Transform → Split Column → By Delimiter
- Choose space as delimiter
- Automatic split!

3. Change Data Types

Automatically detect and convert:

• Text to numbers
• Text to dates
• Number formatting

4. Unpivot Data

Convert wide data to long format (great for analysis):

Before:
Product | Jan | Feb | Mar
Apple   | 100 | 120 | 130

After unpivot:
Product | Month | Sales
Apple   | Jan   | 100
Apple   | Feb   | 120
Apple   | Mar   | 130

Power Query: Select columns → Transform → Unpivot Columns

5. Merge Multiple Files

Combine all CSV/Excel files in a folder:

• Data → Get Data → From File → From Folder
• Select folder
• Combine & Transform
• All files merged automatically!

Why Power Query Beats Manual Cleaning

Task	Manual	Power Query
Reusability	Repeat every time	Click "Refresh"
Documentation	None	Every step recorded
Errors	Easy to make	Consistent results
Speed	Minutes/hours	Seconds

Best Use Cases

• ✅ Cleaning messy CSV exports
• ✅ Monthly report automation
• ✅ Combining data from multiple sources
• ✅ Reshaping data (pivot/unpivot)
• ✅ Removing/filtering rows based on rules

Quick Start Workflow

1. Data tab → Get Data → From Table/Range
2. Make your transformations (each step is recorded)
3. Close & Load to bring cleaned data into Excel
4. When new data arrives → Right-click table → Refresh