Graphed LogoGraphed
Login

How to Remove Filtered Rows in Power BI

Cody Schneider•

Working with filters in Power BI feels intuitive - until you want to make those filters permanent. Simply hiding unwanted data in a report is one thing, but completely removing filtered-out rows from your dataset is a different task that unlocks better performance and clearer reports. This tutorial walks you through exactly how to do that using the Power Query Editor, ensuring your final report only contains the data you need.

Why Remove Filtered Rows Permanently?

You might wonder why you’d go through the trouble of deleting rows instead of just using a slicer on your report page. Filtering on the report canvas is great for interactive analysis, but permanently removing rows in the background offers a few distinct advantages:

  • Improved Performance: This is the biggest reason. Power BI runs faster on smaller datasets. When you remove millions of irrelevant rows, your DAX calculations, visuals, and user interactions become noticeably quicker because the engine has less data to process.

  • Increased Clarity: Sometimes you genuinely don't need historical, irrelevant, or test data in your final report. Removing it at the source eliminates confusion and ensures anyone using the report is focused only on the information that matters.

  • Easier Data Modeling: Stripping out unnecessary rows can simplify subsequent data transformation steps, calculations, and the relationships you build in your data model. A cleaner dataset is an easier dataset to work with.

  • Reduced File Size: A Power BI file (.pbix) with less data is smaller and easier to share, manage, and back up.

The Common Mistake: Filtering in the Report View

Before jumping into the correct method, let's clear up a common misunderstanding. Applying a filter directly on your report canvas (in the Report View) does not delete data from your model. It only hides it from a specific visual or page.

Think of it as a temporary mask. The underlying data is still there, loaded in memory, and included in calculations that aren't affected by that specific filter. If your goal is to permanently reduce the size of your dataset and improve overall performance, you need to go a level deeper.

How to Remove Filtered Rows Using Power Query

The correct way to permanently delete rows based on a filter is by using the Power Query Editor. This is Power BI’s data transformation engine, where you clean and shape your data before it ever gets loaded onto your report canvas. Actions you take here are baked into the data refresh process.

Here’s the step-by-step process.

Step 1: Open the Power Query Editor

First, you need to access the Power Query Editor. From the main Power BI Desktop window, go to the Home tab on the ribbon and click on Transform data. This will open a new window - the Power Query Editor - where all your data shaping magic happens.

Step 2: Select the Query You Want to Modify

On the left side of the Power Query window, you'll see a list of your queries under the "Queries" pane. Each query typically represents a data table. Click on the name of the table from which you want to remove rows.

Step 3: Apply Your Filters to Select the Data You Want to Keep

This part will feel familiar, as it’s just like filtering a column in Excel or Google Sheets. Find the column you want to filter by and click the small drop-down arrow in the column header.

A menu will appear with filter options. Here are a few examples of common filtering scenarios:

Example 1: Filtering by Text

Let's say you have a 'Region' column and you only want your report to analyze data for the "West" region. Click the drop-down on the 'Region' column header, uncheck "(Select All)," and then check the box next to "West." Click OK.

Example 2: Filtering by Date

Imagine your dataset contains sales going back ten years, but you only need data from the last two years. Click the drop-down on your 'OrderDate' column, hover over Date/Time Filters, and choose a relative option like "In the Previous...". You could then specify "2 years" to keep only recent data.

Example 3: Filtering by Number

If you're analyzing large transactions and want to exclude any sale under $50, click the drop-down on your 'Sales Amount' column. This time, hover over Number Filters and select Greater Than.... In the dialog box, enter 50 and click OK.

After you apply any of these filters, the preview pane will update to show only the rows that meet your criteria. The other rows are now hidden within the editor.

Step 4: Check Your "Applied Steps"

This is the most important part of the process. On the right side of the Power Query window, you’ll see the Applied Steps pane. Notice that a new step, labeled something like "Filtered Rows," has just appeared. This confirms that Power Query has recorded your filter action.

This step is Power BI's "recipe" for preparing your data. Every time the data refreshes, Power Query follows these steps in order. By adding the "Filtered Rows" step, you are telling Power BI: "From now on, whenever you refresh this data, go ahead and discard any rows that don’t meet this filter condition."

Inside the editor, this action is still reversible - you can simply click the 'X' next to the "Filtered Rows" step to undo it. But once you apply the changes, the filtered-out data will not be loaded into your model.

Step 5: Click "Close & Apply"

Once you are happy with the filter you’ve set up, go to the Home tab in the Power Query Editor and click the Close & Apply button in the top-left corner. This closes the editor and tells Power BI to apply your entire recipe of changes. Power BI will now re-process the data, and your report’s data model will be updated. The rows you filtered out are now gone from the model - your dataset is smaller, cleaner, and faster.

Best Practices to Keep in Mind

  • You're Making a Hard Commitment: Remember, filtering in Power Query is a semi-permanent decision. The data will be gone from all your report pages and calculations until you go back into the Power Query Editor and remove that filter step. Be sure you won't need that data for other analyses in the same report.

  • Document Your Work: It’s easy to forget why you filtered something six months from now. You can right-click any "Applied Step" and choose Properties to rename the step and add a description. For example, rename "Filtered Rows" to "Remove Sales Before 2022" for clarity.

  • Start with What You Need: For truly massive datasets, you can improve refresh performance even more by filtering your data at the source (e.g., writing your filter into the SQL query) before it even hits Power BI. However, for most users, filtering with Power Query is the most practical solution.

Final Thoughts

Effectively removing unwanted rows from your data model is a fundamental skill for creating optimized and focused Power BI reports. By moving beyond temporary report filters and using the Power Query Editor, you can permanently shape your dataset, leading to faster performance and a better experience for everyone using your report.

Diving into transformation editors like Power Query is powerful but often involves clicking through multiple steps just to get your data ready for analysis. At our company, we wanted to streamline this entire process. With Graphed you can connect your data sources and describe the filters you need in simple language. Instead of navigating menus to filter rows, you can just ask, "build a dashboard showing last year's sales from our Shopify store for the West region," and our AI analyst builds it for you in real time. We automate the technical steps so you can spend less time preparing data and more time acting on it.