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.
 
Removing Unnecessary Rows using Power Query in Power BI
 
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.
 
Removing Unnecessary Rows using Power Query in Power BI
 
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.
 
Removing Unnecessary Rows using Power Query in Power BI
 
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’.
 
Removing Unnecessary Rows using Power Query in Power BI
 
Enter the number of rows that you want to remove from the bottom. I have entered 2.
 
Also I highlighted the rows.
 
Removing Unnecessary Rows using Power Query in Power BI
 
Click on ‘Ok’, and your last two lines will be removed.
 
Removing Unnecessary Rows using Power Query in Power BI
 
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.
 
Removing Unnecessary Rows using Power Query in Power BI
  1. First row to remove
    Enter the row number(index or position) that you want to remove or you want to skip from.

  2. Number of rows to remove
    Enter the number of rows you want to remove each time.

  3. Number of rows to keep
    Enter the number of rows you want to keep.
Removing Unnecessary Rows using Power Query in Power BI
 
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 4th 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.
 
Removing Unnecessary Rows using Power Query in Power BI
 
Click on ‘Ok’ and see the result.
 
Removing Unnecessary Rows using Power Query in Power BI
 
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.
Thanks for reading.