How to Clean Data in Power Query in Power BI: Step-by-Step Guide

To clean data in Power Query in Power BI, use the Power Query Editor to remove unwanted columns, filter rows, replace errors, and change data types. These steps help prepare your data for accurate analysis and reporting.

Syntax

Power Query uses a step-by-step approach where each action creates a new step in the query. Common cleaning actions include:

• Remove Columns: Deletes columns you don't need.
• Filter Rows: Keeps only rows that meet conditions.
• Replace Values: Fixes or standardizes data entries.
• Change Type: Sets correct data types like text, number, or date.

Each step is recorded in the Applied Steps pane and can be edited or removed anytime.

m

let
    Source = Excel.Workbook(File.Contents("data.xlsx"), null, true),
    RemovedColumns = Table.RemoveColumns(Source, {"UnneededColumn"}),
    FilteredRows = Table.SelectRows(RemovedColumns, each ([Status] = "Active")),
    ReplacedValues = Table.ReplaceValue(FilteredRows, "N/A", null, Replacer.ReplaceValue, {"Score"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValues, {{"Score", Int64.Type}})
in
    ChangedType

Example

This example shows how to clean a sales data table by removing an unnecessary column, filtering for active customers, replacing "N/A" with blank in the Score column, and setting Score as a number.

m

let
    Source = Table.FromRecords({
        [Customer="Alice", Status="Active", Score="85", UnneededColumn="X"],
        [Customer="Bob", Status="Inactive", Score="N/A", UnneededColumn="Y"],
        [Customer="Carol", Status="Active", Score="90", UnneededColumn="Z"]
    }),
    RemovedColumns = Table.RemoveColumns(Source, {"UnneededColumn"}),
    FilteredRows = Table.SelectRows(RemovedColumns, each ([Status] = "Active")),
    ReplacedValues = Table.ReplaceValue(FilteredRows, "N/A", null, Replacer.ReplaceValue, {"Score"}),
    ChangedType = Table.TransformColumnTypes(ReplacedValues, {{"Score", Int64.Type}})
in
    ChangedType

Output

[
  {"Customer": "Alice", "Status": "Active", "Score": 85},
  {"Customer": "Carol", "Status": "Active", "Score": 90}
]

Common Pitfalls

Common mistakes when cleaning data in Power Query include:

• Removing columns or rows without checking impact on analysis.
• Not changing data types, causing errors in calculations.
• Replacing values incorrectly, such as replacing partial matches unintentionally.
• Forgetting to apply changes by clicking Close & Apply after editing.

Always preview data after each step to ensure cleaning works as expected.

m

/* Wrong: Replacing partial matches unintentionally */
Table.ReplaceValue(Source, "A", "", Replacer.ReplaceText, {"Customer"})

/* Right: Replace exact value "N/A" only */
Table.ReplaceValue(Source, "N/A", null, Replacer.ReplaceValue, {"Score"})

Quick Reference

Action	Power Query Function	Description
Remove Columns	Table.RemoveColumns(table, {"ColumnName"})	Deletes unwanted columns.
Filter Rows	Table.SelectRows(table, each condition)	Keeps rows matching condition.
Replace Values	Table.ReplaceValue(table, old, new, Replacer.ReplaceValue, {"Column"})	Replaces specific values.
Change Data Type	Table.TransformColumnTypes(table, {{"Column", Type}})	Sets correct data types.

Key Takeaways

Use Power Query Editor steps like Remove Columns and Filter Rows to clean data easily.

Always set correct data types to avoid errors in reports.

Preview data after each cleaning step to catch mistakes early.

Replace values carefully to avoid unintended changes.

Click Close & Apply to save and load cleaned data into Power BI.