Removing Unnecessary Rows Using Power Query In Power BI

Introduction

We can perform many tasks through a power query. One of the most popular tasks that we can perform in a power query is ‘removing unnecessary rows’. Here in this article, I going to show you how to remove unwanted rows.

Step 1 - Remove from Top

In the first step I am showing you how to remove rows from the top. Open power query window by click on ‘Transform Data’ at the home page. The power query editor will be open. Go to ‘New source’ and select any of data resources. I am selecting CSV for the first step. Import CSV file into power query editor. I have 17 rows in my table.

Removing Unnecessary Rows using Power Query in Power BI

To remove rows from the top go to the ‘Home’ tab, click on the ‘Reduce Rows’ drop-down menu. Click on ‘Reduce Rows’ again and you will get 6 options.

Click on ‘Remove Top Row’ and enter the number of rows that you want to remove from the top and click on ‘Ok’. I want to remove the first two rows.

So, I have entered 2. I have also highlighted the first two rows.

After click on ‘Ok,’ the top two rows have removed now I have only 15 rows in my table. See below.

Step 2 - Remove from Bottom

In the second step I am going to remove rows from the bottom. To do so, click on ‘Remove Bottom Rows’.

Enter the number of rows that you want to remove from the bottom. I have entered 2.

Also I highlighted the rows.

Click on ‘Ok’, and your last two lines will be removed.

Step 3 - AlternateRows

The third option we have ‘Remove Alternate Row’. This is required when you want to remove the row from the middle. Once you click on this you will get three fields to be filled.

First row to remove
Enter the row number(index or position) that you want to remove or you want to skip from.
Number of rows to remove
Enter the number of rows you want to remove each time.
Number of rows to keep
Enter the number of rows you want to keep.

Here I entered 3, 2, and 4 respectively. I have a total of 13 rows, see the first column, BussinessEntityId is starting from 276 and ends with 288. So If I click on ‘Ok’ the 3rd and 4^th row i.e. 278 and 279 will be removed (because I entered 2 rows to be removed from the 3rd position) and only 4 more records should be kept and next two records will also be removed. See I have highlighted the rows that will be removed.

Click on ‘Ok’ and see the result.

Here you can see the marked rows were removed.

Summary

I hope you understand and enjoyed this article. Try the above steps. I have mentioned in the first step that how to open a power query editor and import CSV, but didn’t show you further than this. If you want to see the steps, click on the below link.

Power Query In Power BI

Thanks for reading.