Power Query: Data Cleaning, How to Remove Errors, Duplicates, Rows & Columns

If you’ve ever opened a dataset and felt overwhelmed by blank rows, duplicate entries, or cryptic error messages, you’re not alone. Raw data is rarely clean—and that’s exactly why Power Query exists.

In this tutorial, you’ll learn how to remove unnecessary rows and columns, eliminate duplicates, and handle error data in Power Query. Whether you're working in Excel or Power BI, these techniques will help you transform chaos into clarity.

Why Data Cleaning Is Non-Negotiable

Before you build dashboards or run calculations, your data needs to be clean. Here's why:

• Errors break formulas and visuals

• Blank rows and columns slow performance

• Duplicates distort totals and averages

• Irrelevant data leads to misleading insights

Think of Power Query as your data detox tool—cleansing your dataset so it’s lean, accurate, and analysis-ready.

What You’ll Learn to Remove

Type of Data Issue	Description	Example
Blank Rows	Rows with no meaningful data	All columns empty
Unnecessary Columns	Columns not needed for analysis	“Notes”, “Timestamp”, etc.
Duplicate Rows	Repeated entries that skew results	Same customer listed twice
Error Values	Invalid data due to failed transformations	#ERROR, Expression.Error

Data analysis in progress: The image shows a Power Query Editor screen where data for various countries is being filtered and sorted, highlighting Brazil's forecast data for the year 2025.

Removing Unnecessary Rows

Remove Blank Rows

To remove rows where all columns are blank:

• Go to Home → Remove Rows → Remove Blank Rows

• Or filter out rows where key columns are null

Use filters to target specific columns if only partial blanks matter.

Remove Top or Bottom Rows

Imported files often include headers, footers, or notes. To remove them:

• Use Home → Remove Rows → Remove Top Rows or Remove Bottom Rows

• Specify the number of rows to skip

Example:

Table.Skip(Source, 2)

Preview your data to identify which rows are non-essential.

The image showcases Microsoft Excel's Power Query Editor with a focus on an advanced script. The script window displays steps for transforming data, including removing errors and columns. Additional query settings and tools for column management are prominently highlighted in the interface.

Removing Unnecessary Columns

Columns like “Year2”, “Estimate2”, or “Year3” may not be relevant to your analysis.

How to Remove Columns

• Select the columns you want to keep

• Go to Home → Remove Columns → Remove Other Columns

• Or select unwanted columns and choose Remove Columns

Example:

Table.RemoveColumns(Source, {"Estimate", "Year2", "Estimate3", "Year4"})

Keep only the columns that drive insights.

Utilizing Power Query Editor in Excel, this image depicts the process of data transformation with steps to remove duplicates, errors, and unnecessary rows. The Advanced Editor window showcases the M code script crafted for these operations, ensuring data is clean and organized for efficient analysis.

Removing Duplicate Rows

Duplicates can inflate totals or distort averages.

How to Remove Duplicates

• Select the columns that define uniqueness (e.g., “Country / Territory”)

• Go to Home → Remove Rows → Remove Duplicates

Be precise—removing duplicates without selecting the right columns can delete valid data.

Power Query editor interface in Excel displaying a table named "Table1." The image highlights the process of removing errors from a dataset that includes columns for countries and estimates. The Advanced Editor shows the M code script used to apply various transformations and cleaning steps.

Removing Error Data

Errors often appear after type conversions, merges, or calculations.

How to Remove Errors

• Select the column with errors

• Go to Home → Remove Rows → Remove Errors

Or use M code:

Table.RemoveRowsWithErrors(Source, {"Forecast"})

Check for error icons in column headers after transformations.

Bonus: Replacing or Filling Nulls

Sometimes you don’t want to remove nulls—you want to fix them.

Power Query Data Transformation in Excel: The image showcases the use of Power Query in Excel to transform data from "Table1." User actions highlighted include replacing values, sorting rows, and editing in the Advanced Editor. The Query Settings pane displays the applied steps, while the code snippet shows operations like removing errors and duplicates.

Replace Nulls

• Use Transform → Replace Values → Replace null with a default value

Using Power Query in Excel, the image displays an advanced editor interface where a script is used to process a table by removing duplicates and filling down values in the "Country / Territory" column.

Fill Down or Up

• Use Transform → Fill Down or Fill Up to propagate values

Example:

Table.FillDown(#"Removed Duplicates",{"Country / Territory"})

Useful for grouped or hierarchical data.

Best Practices for Data Cleaning

• Always preview your data before cleaning

• Document each step in the Applied Steps pane

• Use dynamic filters instead of hardcoded values

• Test cleaned data with sample visuals or summaries

• Save intermediate queries for reuse and auditing

Ready to Clean Smarter?

Now it’s your turn.

Open Power Query, load a messy dataset, and try removing blank rows, unnecessary columns, duplicates, and errors. Watch your data transform into something powerful.

Here is this lesson's example:

World nominal GDP-2.xlsx

Download XLSX • 27KB

Want more Power Query mastery? Check out:

• [How to Detect and Fix Data Type Issues]

• [Creating Conditional Columns for Smarter Filtering]

• [Using Query Folding to Speed Up Performance]

Got a stubborn dataset or a weird error? Drop a comment or reach out—I’d love to help.

Final Thoughts

Cleaning data isn’t just a technical step—it’s the foundation of every reliable report and confident decision. Power Query gives you the tools to do it fast, smart, and repeatably.

So next time your dataset looks like a mess, don’t panic. You’ve got the skills—and the tools—to clean it up and make it shine.