๐งน 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.