🚀 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:

john@gmail.com

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.