How to Remove Rows in Power BI Query Editor

Cleaning your data in Power BI often starts with removing unnecessary rows. This guide will walk you through several easy-to-follow methods directly within the Power Query Editor, covering everything from deleting blanks and duplicates to filtering based on specific conditions.

First, a Quick Intro to the Power Query Editor

Before we start removing rows, it's important to know where you are. The Power Query Editor is a data transformation tool inside Power BI (and Excel). Think of it as your workshop for getting data ready for analysis. When you load a data source into Power BI, you'll almost always make a pit stop here first. It's where you clean, shape, and transform your raw data into a clean, structured table that's ready for visualization.

To open it, click on the ‘Transform data’ button on the Home ribbon in the main Power BI Desktop window.

Why Is Removing Unwanted Rows So Important?

Your dashboards and reports are only as good as the data powering them. Rows of irrelevant, duplicate, or blank data can throw off your Slicers, skew your calculations, and lead to misleading insights. Here are a few common reasons you'll need to clean house:

• Imported File Headers/Footers: Exports from other systems, especially CSV or text files, often include extra header or summary rows at the top or bottom that aren't part of the actual data table.
• Blank Rows: These often sneak into datasets from manual data entry or malformed exports and can cause gaps in your charts and errors in your calculations.
• Duplicates: A system glitch or data entry mistake might create multiple records for the same event (e.g., two identical order entries). These will inflate totals and mess with your counts.
• Errors: Data type mismatches or bad calculations can produce rows filled with errors that need to be removed or investigated.
• Irrelevant Data: Your dataset might contain records you want to exclude from the report, like test transactions, sales from a closed region, or data outside a specific time frame.

Cleaning this up sounds tedious, but Power Query makes it surprisingly straightforward. Let’s look at the most common methods.

7 Ways to Remove Rows in the Power Query Editor

We'll cover several techniques, from single-click commands to more specific, condition-based filtering. All these actions happen in the Power Query Editor window.

1. Remove Top or Bottom Rows

This is the perfect tool for getting rid of those annoying header and footer rows commonly found in exports. Power Query has dedicated buttons just for this purpose.

How to Remove Top Rows:

1. Navigate to the Home ribbon in the Power Query Editor.
2. In the Reduce Rows section, click the Remove Rows dropdown.
3. Select Remove Top Rows.
4. A small dialog box will pop up asking for the number of rows you want to remove from the top. Enter a number (e.g., 3 if you have three header rows) and click OK. Power Query will immediately remove those rows.

How to Remove Bottom Rows:

1. Follow the same path: Home > Remove Rows.
2. This time, select Remove Bottom Rows.
3. Enter the number of rows to remove from the bottom of your dataset and click OK.

These actions are recorded in the "Applied Steps" pane on the right, so you can easily undo the step if you make a mistake.

2. Remove Alternate Rows

This method is less common but can be useful for tasks like sampling, where you only want to keep every nth row. For instance, you could remove alternating rows to reduce a large dataset by half for performance testing.

How to Remove Alternate Rows:

1. Go to the Home tab and click the Remove Rows dropdown.
2. Choose Remove Alternate Rows.
3. A dialog box will appear with three fields:
4. For example, to remove every other row starting with the second row, you'd enter '1' for the first row, '1' for rows to remove, and '1' for rows to keep.
5. Click OK, and the pattern will be applied.

3. Remove Blank Rows

Blanks are one of the most common data quality offenders. A row might be completely blank, or key columns might contain 'null' values. Power Query offers a simple way to get rid of rows where the entire record is blank.

How to Remove Blank Rows:

1. Head to the Home tab and click Remove Rows.
2. Select Remove Blank Rows.

This single click inspects every row and removes any that consist entirely of 'null' or empty values. However, if a row just has a few blank cells but contains data in other columns, this command won't remove it. For that, you'll need to use filtering, which we cover next.

4. Remove Duplicate Rows

Duplicate entries can completely throw off your metrics. Power Query can identify and remove rows that are identical to each other.

How to Remove All Duplicate Rows:

1. Click the Remove Rows dropdown menu on the Home tab.
2. Select Remove Duplicates.

This action checks the entire row. If Row 5 is identical to Row 20 across all columns, Power Query will keep the first instance (Row 5) and discard the second (Row 20).

How to Remove Duplicates Based on Specific Columns:

Sometimes, a duplicate is defined by a specific key, like an Order ID or Email Address, rather than the entire row being identical. You might have two entries with the same Order ID but a different timestamp, and you only want to keep the first one.

1. Select the column (or columns, using Ctrl+Click) that defines a duplicate. For example, select the 'OrderID' column.
2. Right-click on the selected column header.
3. From the context menu, select Remove Duplicates.

Now, Power Query will only scan the selected column(s). It will keep the first row it finds for each unique Order ID and remove all subsequent rows with that same ID.

5. Remove Rows with Errors

Errors often pop up when you change a column's data type, like trying to convert a piece of text (e.g., "N/A") into a number. Power Query flags these cells with an 'Error' value and gives you an easy way to clear them out.

How to Remove Rows with Errors:

1. Go to the Home tab and find the Remove Rows option.
2. Select Remove Errors.

This will scan all columns in your table and remove any row that contains at least one cell with an 'Error' value. This is a quick way to clean up your data, but be careful - you might be hiding a deeper issue with your data source if certain rows are consistently failing.

6. Filter Rows Based on Cell Values (The Most Common Method)

The concepts we've covered so far are great, but the most powerful and common way to remove rows is by filtering. Instead of telling Power Query which row numbers to remove, you tell it which data to keep, and it removes everything else.

Every column header in the Power Query Editor has a filter button (a small dropdown arrow).

Example 1: Remove rows based on a text value.

Imagine you have a 'Status' column with values like 'Completed', 'In Progress', and 'Test'. To remove all the test entries:

1. Click the filter arrow on the 'Status' column header.
2. In the dropdown list, uncheck the box next to 'Test'.
3. Click OK.

Power BI will "remove" all rows where the status was 'Test'. Technically, it applied a filter to only show you rows that are NOT 'Test'.

Example 2: Remove rows based on a number.

Let's say your sales data includes $0 transactions you want to exclude.

1. Click the filter arrow on your 'Sales Amount' column.
2. Hover over Number Filters and select Does Not Equal....
3. In the dialog box, enter '0' and click OK.
4. Alternatively, you can uncheck '0' from the list if it's visible.

7. Combining Filters for More Complex Logic

You can apply filters to multiple columns to build more detailed logic. For example, you could filter the 'Status' column to keep only 'Completed' orders, and then filter the 'Amount' column to keep only values greater than $100.

Each filter you apply will be added as a 'Filtered Rows' step in the "Applied Steps" pane. This gives you a clear audit trail of how the data is being shaped. You can click on the gear icon next to any step to edit the logic or click the 'X' to remove the filter entirely.

Understanding the "Applied Steps" Pane

As you perform these actions - removing the top 5 rows, filtering out duplicates, etc. - Power Query logs each transformation in the "Applied Steps" pane on the right-hand side. This is your secret weapon for safe data cleaning.

• Made a mistake? Just click the 'X' next to the last step to undo it.
• Need to change a filter? Click the gear icon next to the "Filtered Rows" step to adjust it.
• Want to remember what you did? The descriptive step names (e.g., "Removed Top Rows", "Filtered Rows") provide a clear, chronological history of your transformations.

Once you are happy with your cleaned data, just click 'Close & Apply' on the Home ribbon, and your freshly prepared table will be loaded into Power BI, ready for you to build amazing visuals.

Final Thoughts

You’ve seen several effective ways to remove rows in the Power Query Editor, from simple top/bottom removals to powerful conditional filtering. Mastering these core data cleaning techniques is fundamental for reliable analysis in Power BI, ensuring your reports are accurate and insightful.

Cleaning data by clicking through menus is a common friction point in reporting. At Graphed , we feel data analysis shouldn't require so many manual steps. Instead of performing this cleanup yourself, you can simply connect your data sources to our platform and ask for the report you need in plain English. We handle the complex filtering and data wrangling to give you an intelligent, actionable dashboard instantly.