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.
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
STEP2. Now you have Power Query Editor.
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
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
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
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.
There you go with your result; a new Excel table with the cleaned-up rows.