Data Cleaning Techniques in Power Query

Data cleaning (or data cleansing) is the process of fixing or removing incorrect, incomplete, duplicate, or improperly formatted data. Clean data leads to accurate reports and reliable insights.

1. Handling Missing Values (Nulls & Blanks)

Missing data can distort analysis. Power Query provides several ways to handle it.

Identifying Missing Values: • Null values appear as null in the data preview • Blank cells from Excel appear as empty strings or null • Column quality indicator (View tab → Column Quality) shows % of valid, error, and empty values

Methods to Handle Missing Values:

Steps to Replace Null Values:

1. Select the column
2. Transform → Replace Values
3. In "Value to Find" enter null
4. In "Replace With" enter your default value (e.g., 0)
5. Click OK

Steps to Fill Down:

1. Select the column
2. Transform → Fill → Down
3. Null cells are filled with the nearest non-null value above

2. Removing Duplicates

Duplicate rows can inflate metrics and skew analysis.

Steps to Remove Duplicates:

1. Select the column(s) to check for duplicates
2. Home → Remove Rows → Remove Duplicates
3. Power BI keeps the first occurrence and removes subsequent duplicates

Tips: • Select multiple columns to remove duplicates based on a combination • Use "Remove Alternate Rows" to handle patterned duplicates

3. Splitting & Merging Columns

Splitting a Column: • Useful when a single column contains combined data (e.g., "FirstName LastName" in one column)

Steps:

1. Select the column
2. Transform → Split Column → Choose method:

• By Delimiter (comma, space, colon, custom)
• By Number of Characters
• By Positions

1. Configure options and click OK

Merging Columns: • Combine two or more columns into one

Steps:

1. Select the columns to merge (Ctrl + Click)
2. Transform → Merge Columns
3. Choose a separator (space, comma, hyphen, custom)
4. Name the new merged column
5. Click OK

4. Changing Data Types

Correct data types are essential for accurate calculations and visualizations.

Common Data Types:

Steps to Change Data Type:

1. Click the data type icon on the column header (ABC, 123, etc.)
2. Select the desired data type from the dropdown
3. Or: Select column → Transform → Data Type → Choose type

Important: Always set data types correctly before loading data. Incorrect types cause errors in DAX calculations.