🧹 Excel — Level 9: Power Query for Data Cleaning & Transformation
So far, you've learned how to analyze data using Excel formulas and PivotTables.
But there's a major problem with real-world data:
The data is often messy.
You might receive a monthly Excel file with:
• Duplicate records
• Missing values
• Incorrect data types
• Extra spaces
• Inconsistent names
• Multiple files
• Unnecessary columns
• Data spread across different tables
Cleaning this manually every time is slow and error-prone.
That's where Power Query comes in.
1️⃣ What Is Power Query?
Power Query is a data preparation and transformation tool available in Excel and Power BI.
It allows you to: Connect → Extract → Transform → Load - This is commonly called ETL.
Extract: Get data from a source.
Transform: Clean and reshape the data.
Load: Bring the prepared data into Excel for analysis.
The biggest advantage is repeatability. Instead of cleaning the same file manually every month, you create a transformation process once and refresh it.
2️⃣ Why Should a Data Analyst Learn Power Query?
Imagine your company sends you this file every month:
January.xlsx, February.xlsx, March.xlsx, April.xlsx...
Every file contains 50,000 rows, extra spaces, duplicates, incorrect date formats.
Without Power Query, you repeat the same cleaning every month.
With Power Query: Refresh → Transformations run again
3️⃣ Where Do You Find Power Query?
In modern Excel: Data → Get & Transform Data
Options: From Table/Range, From Workbook, From Text/CSV, From Folder, From Web, From Database
4️⃣ Understand the Power Query Workflow
Data Source → Connect → Power Query Editor → Clean → Transform → Validate → Load → Excel / Data Model → Analysis
Power Query records the transformation steps.
5️⃣ Import Data from Excel & CSV
Excel: Data → Get Data → From File → From Excel Workbook → Select sheet → Open in Power Query Editor
CSV: Data → From Text/CSV → Preview delimiter, headers, data types → Transform Data
6️⃣ Power Query Editor
Left side: Queries
Middle: Data preview
Right side: Applied Steps
Example Applied Steps:
Source → Changed Type → Removed Columns → Filtered Rows → Removed Duplicates → Renamed Columns → Added Custom Column
7️⃣ Changing Data Types
Correct data types are critical.
Order ID → Whole Number, Order Date → Date, Sales → Decimal Number, Customer → Text
Use the data-type icon to change it.
8️⃣ Remove Duplicates
If Order ID should be unique, select the column and use: Remove Rows → Remove Duplicates
🔟 Important: Understand What a Duplicate Means
Don't automatically delete duplicates.
Ask: > Is this actually a duplicate?
Two records with same customer but different orders = Not a duplicate.
Same order appearing twice = Duplicate.
1️⃣1️⃣ Remove & Rename Columns
Remove unnecessary columns: Home → Remove Columns
Rename for clarity: CustNm → Customer Name, SlsAmt → Sales
1️⃣2️⃣ Filter Rows
Filtering in Power Query becomes part of the reusable query.
Example: Keep only orders from 2026, or North region, or Sales > 0
1️⃣3️⃣ Handle Missing Values
Never blindly replace missing values with zero.
