Removing rows from a Power BI dataset is typically done at the data model level using Power Query. An alternative option is to apply filter criteria to a page if you want to keep the primary dataset intact and only remove rows for a specific portion of your report. We’ll explore both methods and explain when each one might be appropriate.
When deleting rows from Power BI it’s important to understand that Power BI does not write back to the initial dataset, meaning that you can delete rows from incoming data using Power BI without impacting the source system and there isn’t much you can do in Power BI that can’t be undone.
Let’s dive in!
Table of Contents
What is Power BI Power Query?
Power BI Desktop has two primary sections. One section is the report builder where users create charts, graphs, and tables that report viewers see. The other section is Power Query, where data is imported, cleaned, prepped and transformed before being sent to the visualization side of Power BI where the visuals are made.
We wanted to highlight Power Query, because it is the most common way to remove rows from Power BI. The less rows you have going from the data prep stage to the report building stage means that your report will run faster when users click through them and it’s best setup for data prep.
To launch the Power Query editor in Power BI, click on Transform Data from the Home Tab of the Power BI Ribbon.
Once open, let’s look at how to remove some rows.
How to Remove Rows in Power BI
Power BI developers can use the Power Query editor to remove rows. You can remove top rows, bottom rows, and duplicates with the Remove Rows button. You can also filter out the unwanted rows based on their content or using conditional formulas. An index column can be used to remove rows based on a specific row number.
We’ll take a look at all three methods.
Using the Remove Rows Button in Power Query
After launching the Power Query editor, you can use the Remove Rows button, located on the Home tab to remove top rows, remove bottom rows, duplicate rows and errors. Removing top and bottom rows is especially useful for removing headers and footers with a set number of rows in a dataset.
When you select Remove Top Rows or Remove Bottom rows you will be prompted with an option to designate how many rows you want to remove. This will skip the first or last N number of Rows that you enter into the box.
Removing Alternate rows gives you the ability to designate how many rows and how often you wish to skip rows. It’s especially useful when you need to clean data prep removing every other row when starting data comes in an odd format.
Removing Duplicates and Errors in Power BI Power Query
Removing duplicates and errors in Power Query is slightly different than other remove row options. It’s done based on the column that you have selected before pushing the button. In the example below, we have the Cookie Name column selected and when telling Power Query to remove duplicates it drops all other rows beyond the first instance of a value.
Tip: Many functions in Power Query can be performed by right clicking on a column header. You will see a contextual menu that has many of the most popular actions available, such as the ability to remove duplicates and remove errors.
Now lets look at how to remove rows based on their contents.
Removing Rows in Power BI Based on Row Contents
Removing rows in Power BI is specific to the remove row functions that either skip the first or last rows, alternating rows or can detect duplicates. If you want to remove rows based on the values inside of a column, you actually have to Filter the dataset.
The fastest way to apply a filter is to click the small box at the top of a column. You can either unselect all, or select specific values.
The example below filters the data to only show the Chocolate Chip value, and a new Applied step is created for Filtered Rows on the right side of the screen.
When you close out of Power Query, the dataset at the end of the Applied Steps is the data that becomes available to create reports, visuals, charts and graphs off of. While the data isn’t necessarily deleted, it will effectively be removed for all of the visuals and the reporting side of Power BI.
How to Delete Rows Based on Conditions in Power BI
To conditionally remove rows in Power BI, create a new conditional column in Power Query that returns a value when a condition is met. Then apply a filtering step to filter out the rows that met the condition. In this method, you can define multiple conditions and remove them all with a single step.
To create a conditional column, click the Conditional Column button from the Add Column tab of Power Query. Then define your specific criteria. In the example below we entered multiple criteria to create a new “Filter Column” that either returns a 1 or a 0 based on whether it meets the criteria.
The second step is to filter out any of the rows that met the filtering criteria, or did not meet the criteria based on how you setup your conditional column.
For more advanced conditions that what is available using the conditional column editor, you can also create custom columns using Power Query M formulas.
How to Delete Specific Rows in Power Query
To delete specific rows in Power Query, you can add an index column and then filter out the row number. The index column creates a new column that starts with 1 or 0 and fills down the entire Power BI dataset. You can then filter the index column based on a specific row to remove it.
We don’t actually recommend this method, as the specific row that data comes in on is subject to change. The row you filter out today could be different when the data is refreshed meaning that a quick fix today could potentially cause incorrect information the next time the data is updated.
Removing Rows with Power Query vs Filtering Visuals
Power Query is the standard method for removing rows and columns from a dataset. By reducing the amount of data that goes into the report side of Power BI, reports will run faster when a report viewer clicks on a visual to drill down into it or applies a slicer to filter on a specific view. The less data in a data model, the faster Power BI can perform the necessary calculations.
However, if you need to selectively remove rows from a visual, you can apply filters to the visual or to a page using the Power BI Filter Panel.
The screenshot below shows a Matrix Visual selected, and the Chocolate Chip category unselected in the Filter Pane which effectively removes that row from the visual.
Using a visual filter can be beneficial when you want to control the view for a specific page of your report and do not want to completely remove the row from your entire dataset. Removing or Filtering in Power Query will completely remove the data from being available to build charts and graphs in the report builder.
We specifically cover how to How to Add or Remove Blank Rows in Power BI Matrix Visuals as it is a common use case.
Removing Rows in Power BI vs Source Data
Power BI makes it fast and easy to remove rows in a number of ways. However, if you are removing large numbers of rows, it’s worth considering filtering the data before it comes into Power BI to optimize performance. The less data that power BI has to import, the faster the import will go and the fewer data transformation steps it will have to process.
If you’re importing data using a database and SQL, we highly recommend the following article on How to Write a WHERE Clause in SQL. Where clauses act as filters to limit the amount of data a query pulls into Power BI. In today’s cloud first world many cloud providers charge for the amount of data that leaves their data warehouses meaning that SQL WHERE clauses can actually save you money!
Removing and filtering rows in Power BI are mostly interchangeable. Because steps applied in Power Query control the data that’s available in the report editor, rows that are filtered are effectively removed. To skip first and last or a set number of rows, you can use the Remove Rows feature.
For more control of which rows are removed from a dataset they can be filtered out based on the contents or various conditions. Advanced conditional column removal can be performed using a new conditional column to tag which rows meet conditions and can be filtered out all at once.
Specific columns can be removed using an index column and filtering out the row number.
Then finally, consider where your data is coming from. It can be beneficial to remove rows prior to importing it into Power BI to reduce costs and reduce the amount of additional time you have to spend preparing and cleaning it before sending it on its way to build reports.