🚀 Data Analyst Roadmap — Part 23
📊 Power BI Level 2 — Power Query: Data Cleaning & Transformation
Power Query is used in Power BI to clean, transform, and prepare data before building reports.
🔹 1. Open Power Query
In Power BI Desktop:
Home → Transform Data
This opens the Power Query Editor.
You will mainly work with:
• Queries
• Data Preview
• Applied Steps
🔹 2. Change Data Types
Always check whether columns have the correct data type.
For example:
Customer_ID → Text
Quantity → Whole Number
Sales → Decimal Number
Order_Date → Date
Incorrect data types can cause problems in calculations and visuals.
🔹 3. Remove Unnecessary Columns
If your dataset contains columns you don't need, remove them.
For example:
Customer_ID
Customer_Name
Email
Phone
Sales
Internal_Code
If your analysis only needs Customer ID, Customer Name, and Sales, remove the rest.
🔹 4. Filter Unnecessary Rows
Power Query can remove or filter:
• Blank rows
• Invalid records
• Test data
• Unwanted categories
• Records outside the required period
Always understand the business rule before removing data.
🔹 5. Remove Duplicates
Power Query allows you to remove duplicate values based on selected columns.
For example, if "Customer_ID" should be unique in a Customer table, duplicate IDs should be investigated.
But don't remove duplicates blindly.
A Sales table can naturally contain many rows for the same customer.
🔹 6. Handle Missing Values
You may find:
Blank
NULL
N/A
Unknown
Depending on the situation, you can:
• Keep the value blank
• Replace it
• Remove the record
Don't automatically replace blanks with zero.
For example, a blank discount doesn't always mean a discount of 0.
🔹 7. Clean Text
Data often contains unwanted spaces or inconsistent formatting.
Example:
" Mumbai"
"Mumbai "
"MUMBAI"
Useful Power Query transformations include:
Trim → Removes unnecessary spaces
Clean → Removes unwanted non-printable characters
You can also change text to:
• UPPERCASE
• lowercase
• Proper Case
🔹 8. Replace Values
Suppose your data contains:
Mum
Mumbai
MUMBAI
You can replace and standardize values so they are represented consistently.
This is especially useful for:
• City
• Region
• Category
• Department
• Status
🔹 9. Split Columns
Suppose you have:
Full Name
John Smith
Sarah Johnson
You can split it into:
First Name | Last Name
John | Smith
Sarah | Johnson
You can split a column using delimiters such as:
• Space
• Comma
• Dash
• Custom delimiter
🔹 10. Extract Text
You can extract specific parts of a text column.
For example:
[email protected]
You could extract:
john
or:
gmail.com
Power Query provides options such as:
• Text Before Delimiter
• Text After Delimiter
• Text Between Delimiters
• First Characters
• Last Characters
🔹 11. Conditional Column
You can create categories based on conditions.
For example:
Sales >= 50,000 → High
Sales >= 20,000 → Medium
Otherwise → Low
This is similar to "CASE WHEN" in SQL.
🔹 12. Custom Column
Power Query also allows you to create calculated columns.
For example:
Total Amount = Quantity × Unit Price
Custom columns use Power Query's formula language, called M.
