Data manipulation refers to a set of tasks that play pivotal roles in extracting accurate and meaningful insights from unprocessed data. One of the tasks involved in data manipulation is the deletion or filtering of rows.

Deleting rows refers to the process of removing certain entries from data, usually based on specific conditions. These rows of data are normally removed for several reasons that can include irrelevance to data analysis objective or an error in data entry.

One function row deletion or filtering performs is that it allows you to refine and shape your data. By doing this, you ensure that the information presented is accurate and relevant, and that it aligns with your analytical objectives.

Understanding the processes involved in deleting rows is a crucial skill. This article will expose you to the several ways in which you can delete or filter rows using tools in Power BI.

Delete Rows in Power BI with Power Query

Power Query is a powerful tool that simplifies the process of data management and refinement. One of its capabilities includes the ability to delete and filter rows.

With Power Query, you can effortlessly eliminate inaccurate or irrelevant rows, resulting in an accurate dataset that precisely aligns with your analytical requirements.

To use Power Query to filter or delete rows, follow these steps.

1. Identify the column you want to filter.
2. Select the dropdown arrow beside the column name.
3. Click on the checkmarks to select or deselect the items you want to keep and delete.

After the third step, the number of rows in the data will reduce.

Delete Rows with Parameters

In Power BI, a parameter functions as a dynamic variable capable of altering the output of reports or dashboards. Its versatility allows parameters to serve as tools for data filtering, measure adjustments, and the inclusion of new fields within a report.

You can also use parameters to delete or filter rows in Power Query. Before creating a parameter, you may want to first create a list of the items you want to filter by.

To do this, right-click on the column containing the items by which you want to filter and select the Add as New Query option.

This will create a new query that’s a list of all the items in the column added as a query. What you want from this column is a distinct list of all the items.

To return only the unique items, right-click on the column and select the Remove Duplicates option.

After creating a unique list, right-click again on the list and select the Copy Entire List option.

Now, click on an empty space in the queries tab and select the New Parameter option.

You can also create a New Parameter in the Home tab, under the Manage Parameters option.

In the Manage Parameters window, use the Name option to name the parameter and give it a relatable Description. The Type option lets you select what data type the parameter should accept.

Adding a checkmark to the Required option makes it compulsory for other users to provide a value for the parameter. If a value isn’t provided for the parameter and the Required option is enabled, the parameter won’t work.

The Suggested Values option lets you provide a set of values that the parameter can suggest. This can either be as a List of values or a Query. The Any value option doesn’t provide suggestions, instead, it lets you enter any value for which the parameter will use.

Using a list is quite appropriate for row filtering because the parameter returns a dropdown list of all the values. From there, you can easily select the value with which you want to filter or delete.

When you right-click on the empty table and select Paste, the copied items will fill the rows in the table. You can then set a Default Value and Current Value using the items you just provided in the list. Click on the OK button to save the set-up and close the window.

The parameter will now appear on the query pane carrying the item set as the Current Value in the set-up window. To change the parameter’s current value, simply click on the dropdown arrow and select from the options. You can also type the name of the value into the box.

After creating the parameter, go to the View menu and add a tick to the Always allow option. This will allow you to use parameters.

To delete or filter rows with the parameter you’ve created, follow these steps.

1. Click on the dropdown arrow beside the column name by which you want to filter.
2. Select Text Filters from the options.
3. Select from any of the text filtering options.

4. In the Filter Rows window, click on the Advanced radio button and choose Parameter as the value.

5. You can remove the second filtering condition by clicking on the three dots and selecting the Delete option.

When you click on the OK button after the last step, the selected column will be filtered by the current value of the parameter.

To filter by another value, you simply need to change the current value of the parameter from the query pane.

One downside of using parameter-based filtering is that you can’t select multiple values. Nevertheless, you can simply copy and paste the same parameter if you want to filter using values from the same list.

You simply just right-click on the parameter you want to copy and paste into the query panes. All you then need to do is change the current value and the affected table will be automatically filtered based on the set filtering condition.

Delete Rows in Power BI with Native Power BI Tools

Deleting rows in Power Query offers an effective means to trim down the volume of data brought into Power BI. Alternatively, you can achieve this within Power BI, but it requires that you import the entire dataset.

This approach becomes particularly relevant when your report spans multiple dimensions and necessitates a comprehensive dataset for accurate and cross-dimensional analysis.

To remove rows in Power BI, navigate to the Data view and locate the table from which you wish to delete rows. Then, access the column that contains the items you want to remove. Click on the column’s dropdown arrow.

From the options, you can use the checkboxes or the Text filters option to decrease the number of rows in that column or table. This process is like deleting rows in Power Query without parameters.

Delete Rows with Row Level Security

An effective method for eliminating or minimizing the number of rows in a dataset involves implementing row-level security within your model. Row-level security serves as a data protection mechanism that allows you to control access to specific data.

This is accomplished by filtering data using a DAX statement and assigning the filtered dataset to specific roles. Users are then assigned to the created roles. The addition of users to the created roles restricts their access solely to the filtered or reduced section of the data.

Row-level security offers an excellent solution for effective data access management and control.

💡 Tip: Discover more about how to use row level security in Power BI.

Conclusions

Whether you want to eliminate erroneous entries or irrelevant data, or concentrating on a particular subset of information, the process of deleting rows is pivotal in refining your data model. What’s clear is that removing inaccurate or unwanted records helps to finetune your data model so that it aligns with your analytical objectives.

While Power BI does not permit data manipulation at the row-level or cell-based level as you cannot modify individual cell values independently; this doesn’t hinder your ability to remove undesired or surplus data efficiently.

Power Query allows you to effortlessly refine your dataset through efficient row deletion. It offers various approaches to accomplish this, allowing you to eliminate rows based on parameters or directly choose specific items from any column.

By streamlining data loading, Power Query prevents unnecessary data overload and ensures optimal storage utilization for your system.

That said if you need to remove rows, but you still want to have it in your data model, you can also do that on Power BI using the Data view menu.

Essentially, with Power BI, there’s no shortage of options for removing unwanted data!