How to Remove Rows in Power BI Based on Condition

Working with data often means dealing with bits and pieces you don't actually need. A common task in Power BI is tidying up your tables by removing rows that clutter your analysis. This article will show you several straightforward methods for removing rows based on specific conditions, all inside the Power Query Editor.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Conditional Filtering is Your Best Friend in Power BI

Before jumping into the "how," let's quickly cover the "why." You don't just remove rows for the sake of it. Proper filtering is a foundational step in data preparation that has a huge impact on your final report.

• Improved Accuracy: Getting rid of irrelevant or incorrect data (like test orders, zero-value sales, or error entries) ensures your calculations and metrics are accurate.
• Faster Performance: Power BI reports process data in memory. The fewer rows it has to load, store, and calculate, the faster your dashboards will load and update. Removing 100,000 unnecessary rows can make a noticeable difference.
• Clearer Analysis: When you remove distracting or irrelevant information, you can focus on the data that matters. This leads to clearer visualizations and more effective insights.

In short, cleaning your data is the secret to building high-quality, trustworthy, and snappy reports.

Meet Your Primary Tool: The Power Query Editor

Nearly all permanent data transformations, including removing rows, happen in the Power Query Editor. Think of it as your data workshop separate from your main report canvas. Any changes you make here are applied to your dataset before it ever gets loaded into your report model.

This is a critical distinction: filtering a single visual on your report merely hides data from view, whereas filtering in Power Query removes it from the dataset entirely for that report.

To open the Power Query Editor:

1. In the main Power BI Desktop window, go to the Home tab on the ribbon.
2. Click the Transform data button.
3. A new window will open - this is the Power Query Editor. You'll see your tables listed in the left pane.

Now that you're in the right place, let's look at the different ways to remove rows.

Method 1: The Easy Way with Dropdown Filters

The most familiar and intuitive way to filter rows is by using the dropdown menus in each column header, which work very much like the filter feature in Microsoft Excel.

Imagine you have a sales table and you want to remove all rows where the "Order Status" is "Cancelled".

Step-by-step instructions:

1. In the Power Query Editor, find the column you want to filter (e.g., "Order Status").
2. Click the dropdown arrow (▼) in the column header.
3. A list with all the unique values in that column will appear.
4. Uncheck the box next to the value you want to remove. In this case, you would uncheck "Cancelled".
5. Click OK.

Instantly, all rows where the "Order Status" was "Cancelled" will disappear from your preview. Notice on the right-hand side, in the "Applied Steps" pane, a new step called "Filtered Rows" has appeared. Power Query records every action you take, allowing you to easily undo or edit steps later.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Applying More Specific Filters

The dropdown menu offers more than just ticking boxes. You'll find specific filter menus based on the column's data type (text, number, or date).

Text Filters

Click the dropdown, hover over "Text Filters," and you'll see a list of logical conditions.

• Equals / Does Not Equal: Perfect for removing or keeping an exact match.
• Begins With / Does Not Begin With: Useful for filtering based on prefixes, like removing all SKUs that don't start with "PROMO-".
• Contains / Does Not Contain: Great for finding a keyword anywhere in the text. For example, you could remove all product entries from a certain supplier by filtering out rows where the "Product Name" column contains "SupplierName".

Number Filters

With a numeric column (like "Revenue" or "Quantity"), hover over "Number Filters," and you'll get math-based options.

• Greater Than / Less Than: A classic use case is removing sale amounts that are zero or negative. You could filter the "Revenue" column to show only rows where the value is "greater than" 0.
• Is Between...: Filter for a specific numeric range.
• Is Not Equal To...: Simply exclude a particular number.

Date Filters

In a date column, the "Date/Time Filters" give you powerful, dynamic options.

• Before / After / Between: Remove all data before a specific launch date or after an event ended.
• Relative Dates: You can create dynamic filters like "In the Previous," "In the Next," etc., combined with units like days, weeks, or years. This is fantastic for creating reports that automatically show trailing period data (e.g., always removing dates older than 2 years).

Method 2: Cleaning Up Nulls, Blanks, and Errors

Often, your data isn't just irrelevant - it's incomplete or broken. Empty cells (nulls), entirely blank rows, or import errors can wreck visualizations and calculations. Power Query has dedicated tools to handle these issues efficiently located under the "Remove Rows" button in the Home ribbon.

Removing Blank Rows

Sometimes you’ll import data, especially from CSV or Excel files, that contains completely blank rows between your good data. Trying to filter these out column-by-column is a pain.

1. Go to the Home tab in the Power Query ribbon.
2. Click on the Remove Rows dropdown.
3. Select Remove Blank Rows.

Power Query will scan your entire table and remove any row where every single cell is null or empty. It’s a huge time-saver for quick cleanups.

Removing Errors

Data import errors happen. A text value ends up in a number column, or a dodgy connection messes up some fields, leaving you with [Error] cells. These will cause your entire report refresh to fail if not handled.

The solution is simple:

1. Select the key column that most often contains the errors. A good candidate is usually an ID, an amount, or a critical date column.
2. Go to the Home tab > Remove Rows dropdown.
3. Select Remove Errors.

This action will delete every row where the selected column contains an error, keeping your dataset clean and your refreshes running smoothly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 3: Leveling Up with Custom M Code (For More Control)

The GUI-based filters are great for 90% of situations, but sometimes you need to apply more complex logic. This is where Power Query’s formula language, M, comes into play. Don't be intimidated - you can do a lot with just a tiny bit of M code logic, and Power Query often writes it for you.

When Would You Use More Complex Logic?

The most common case is when your condition involves multiple columns. For instance, what if you want to remove rows where Order Status is "Shipped" but the TrackingNumber is blank? Or remove test sales where the CustomerEmail contains "@test.com" AND the TotalAmount is less than $1?

Let's look at two practical ways to handle this.

A Simple Approach: The Conditional Column

One of the easiest ways to apply custom logic without writing code from scratch is to use the "Conditional Column" feature. The strategy is to first create a helper column that flags rows to be removed, and then filter based on that column.

Let's use the shipping example: remove orders that are "Shipped" but have a blank tracking number.

1. Go to the Add Column tab in the Power Query ribbon.
2. Click on Conditional Column.
3. A new dialog box will open. Fill it out as follows:
4. Click OK. You now have a new helper column!
5. Use the simple dropdown filter (Method #1) on your new "KeepOrRemove" column and filter it to only show rows where the value is "Keep".
6. Finally, right-click the "KeepOrRemove" column header and select Remove to get rid of the helper column you no longer need.

Directly Modifying a Filter Step with M

A more direct and efficient approach is to write the multi-column logic straight into a filter step. Happily, you can see the M code Power Query generates for every GUI action you take. This makes it easy to learn and modify.

To see this in action, first enable the formula bar by going to the View tab and checking the box for Formula Bar.

Now, perform a simple filter - for example, on the "Order Status" column, filter it to just show "Shipped". The formula bar will now show something like this:

= Table.SelectRows(#"Previous Step", each [Order Status] = "Shipped")

This is the M Code! To build on this, you can simply add a standard and or or operator to incorporate logic from a second column.

Let's go back to our same problem: We want to keep any row that is NOT both shipped and missing a tracking number. So the logic for what we want to keep is: Status is not "Shipped" OR the tracking number is not null.

Click on the step you just made and modify the formula bar to look like this:

= Table.SelectRows(#"Previous Step", each not ([Order Status] = "Shipped" and [TrackingNumber] = null))

Breaking it down:

• Table.SelectRows is the function for filtering rows.
• #"Previous Step" is the input, meaning whatever the table looked like before this action.
• each ... starts the condition for each row.
• not ([Order Status] = "Shipped" and [TrackingNumber] = null) is our custom logic. M's syntax is quite intuitive here. Note that blank cells in Power Query are represented by null.

This approach is faster and keeps your "Applied Steps" pane cleaner, leaving you with one powerful filter step instead of three.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Best Practices to Remember

• Always work in Power Query. Remember, for permanent data removal and cleaning, the Power Query Editor is your tool.
• Start simply. The dropdown filters solve most problems. Don't jump to complex methods unless you need to.
• Use the "Applied Steps" pane. This is your history log. If you make a mistake, you can simply click the 'X' next to a step to delete it and go back.
• Check for case sensitivity. When filtering text, "Cancelled" and "cancelled" are two different things. Use the "Transform" -> "Format" -> "Capitalize Each Word" or "lowercase" tools before filtering to standardize your text.
• Document complex logic. If you write an intricate M Code filter, right-click the step in the "Applied Steps" pane, choose "Properties," and describe what it does. Future you will thank you.

Final Thoughts

Cleaning and preparing your data by removing unnecessary rows is a vital skill for anyone working in Power BI. By mastering the easy dropdown GUI filters for simple tasks, using the dedicated tools to clean up blanks and errors, and learning to apply a little custom logic with M, you can transform messy source files into a pristine dataset ready for amazing analysis.

Mastering Power Query is a fantastic skill, but it often involves a lot of clicks, managing steps, and a learning curve to get things just right. We created Graphed to simplify this entire workflow. Instead of going through multiple steps to filter your data, you can just describe what you want in plain English, like "show me all sales from last year but remove any refunded orders." Our platform handles the tricky connections, cleaning steps, and transformations in the background, building live, interactive dashboards for you in seconds. It allows you to skip straight from question to insight, saving you tons of time in the process.