How to Clean and Prepare Data in Power BI Using Power Query

Raw data is rarely ready for reporting. It often contains missing values, inconsistent formats, duplicates, or unnecessary columns. Cleaning and preparing this data is a critical step before building any dashboard.

Power Query provides a structured way to fix these issues and ensure your data is accurate, consistent, and analysis ready.

When Should You Clean Data?

Data cleaning should be done before loading data into the report view, not after.

Starting with clean data reduces errors, improves performance, and avoids rework later.

What Does Data Cleaning Include?

Data cleaning is the process of identifying and correcting issues in a dataset before analysis. In Power Query, data cleaning typically includes:

• Removing unnecessary columns that are not required for reporting or analysis.

• Removing unwanted rows such as blank rows, header rows, totals rows, or test records.

• Handling null and missing values by replacing, filling, or removing them.

• Removing duplicate records to ensure accurate calculations and reporting.

• Removing errors caused by invalid values, incorrect data types, or failed calculations.

• Correcting data types such as converting text to dates, numbers, percentages, or currency values.

• Renaming columns to improve clarity and usability.

• Filtering data to include only relevant records.

• Standardizing formats such as dates, names, and categories.

• Investigating Column Quality, Column Distribution, and Column Profile statistics to identify anomalies, missing values, data patterns, and inconsistencies.

Performing these activities helps ensure that reports, dashboards, and calculations are built on accurate and reliable data.

Step 1: Open Power Query Editor

1. Go to Home → Transform Data

2. Select your dataset (query) from the left panel

You are now ready to start cleaning your data.

Step 2: Remove Unnecessary Columns

Extra columns increase file size and slow down performance.

How to do it:

• Select unwanted columns

• Right-click → Remove Columns

Tip: Keep only the columns required for reporting.

Step 3: Handle Null and Missing Values

Missing data can affect calculations and visuals.

Options:

• Replace nulls with default values (e.g., 0 or “Unknown”)

• Remove rows with null values

• Fill down or fill up values

 Example:

• Replace blank sales values with 0

• Remove rows where key fields (like Employee ID) are missing

Step 4: Remove Duplicates

Duplicate records can distort analysis.

Steps:

• Select the relevant column(s)

• Click Remove Duplicates

This ensures each record is unique where required.

Step 5: Correct Data Types

Power BI relies heavily on correct data types.

Common types:

• Text

• Whole Number

• Decimal

• Date

 How to fix:

• Select column → Change Data Type from the toolbar

Example:

• Convert “Date” column from text to date format

• Convert “Sales” column to numeric

Step 6: Investigate Data Quality Using Column Profile

Power Query provides powerful profiling features that help you understand the quality and structure of your data.

To enable these features:

1. Open the View tab in Power Query.

2. Enable:

   • Column Quality

   • Column Distribution

   • Column Profile

These features help identify:

• Valid, empty, and error values in each column.

• Distinct and unique values.

• Data distribution patterns.

• Unexpected outliers and anomalies.

• Columns with missing or inconsistent data.

Reviewing column profiles before loading data helps detect issues early and improves overall data quality.

Step 6: Rename Columns for Clarity

Clear naming improves usability.

Instead of:

• Column1, Sheet1_Data

Use:

• Employee Name

• Monthly Sales

This helps when building visuals later.

Step 7: Filter and Sort Data

Filtering helps focus only on relevant data.

Examples:

• Filter last 30 days

• Exclude inactive employees

• Remove test or dummy records

Step 8: Group and Aggregate Data

Grouping allows summarizing data.

Steps:

1. Select a column (e.g., Employee Name)

2. Click Group By

3. Apply aggregation:

   • Sum

   • Average

   • Count

Use case:

• Total working hours per employee

• Average sales per region

Aggregation helps convert raw data into meaningful insights.

Step 9: Understand Data Statistics

Power Query provides quick insights like:

• Minimum value

• Maximum value

• Average

• Standard deviation

• Distinct vs Unique values

 Why it matters:

• Helps identify anomalies

• Detects data inconsistencies

Step 10: Apply and Load Data

Once cleaning is complete:

• Click Close & Apply

• Data will be loaded into Power BI

All cleaning steps are saved and will be reapplied during refresh.

Real-World Example

You receive Excel data with:

• Blank rows

• Mixed formats

• Duplicate entries

Using Power Query, you can:

• Remove blanks

• Standardize formats

• Deduplicate records

• Prepare a clean dataset ready for reporting

Best Practices

• Clean data as early as possible

• Keep transformation steps minimal and logical

• Validate data after each step

• Use consistent naming conventions

• Avoid manual edits outside Power BI

Common Mistakes

• Loading unclean data into reports

• Ignoring null values

• Not checking duplicates

• Using inconsistent formats

• Overlooking data types