Remove Rows Using Power Query

In-Office 365, Power Query or Get & Transform provides you the option to perform a series of steps to transform your excel data. There is one most common step I do; is cleaning my data and removing rows and Power Query also gives you so many other options to do.

Previously In Power Query How to Delete Steps until End in Power Query?

You can also browse our Power Query Tutorial Catalog here.

NOTE 1: You can create random excel workbook online here for practice purposes as I did in this tutorial.

NOTE 2: If You use Microsoft Excel Prior to 2016 Version, then You Need to download Power Query Separately.

Here you go with the steps in detail to perform this tutorial:

STEP1. In my sample data it contains the sales number for each month. Firstly I’m going to prepare this data to use in power query.

For Excel 2016: Go to Data > Get & Transform > From Table 

For Excel 2013 & 2010: Power Query > Excel Data > From Table

Remove Rows Using Power Query

STEP2. Now you have Power Query Editor.

Remove Rows Using Power Query

Go to Home > Reduce Rows > Remove Rows

There are many options I’m going to show you these 3 in action:

STEP3. Now in the third step follow this procedure: 

Go to Home > Reduce Rows > Remove Rows > Remove Top Rows

Remove Rows Using Power Query
I’m going to try to remove the first row so I will input 1 as the number of rows and then click OK.

Here you will see that Nickie And D’oyley are now removed from the table. 

STEP4. Go to Home > Reduce Rows > Remove Rows > Remove Bottom

Remove Rows Using Power Query
I’m going to try to remove these bottom two rows so I will input 2 as the number of rows and then click OK.

Here you can see 18 And 19 Rows are now removed from your table.

STEP5. Now follow this: Go to Home > Reduce Rows > Remove Rows > Remove Alternate Rows

This is going to be a little tricky to use but I will find an easy way to tell you. I want to remove the followings Rows: 1, 3 and 5. So for doing the same, I’m going to input the following:

  • First row to remove – 1
  • Number of rows to remove – 3
  • Number of rows to keep – 14

Click OK.

Remove Rows Using Power Query

STEP6. Now in final steps click Close & load from the Home Tab to open up a new sheet in your Excel with all new data.

Remove Rows Using Power Query

There you go with your result; a new Excel table with the cleaned-up rows.