Remove Rows Using Power Query

Power Query lets you perform a series of steps to transform your messy Excel data. One of the most common steps I do is to clean my data and remove rows. Power Query gives you a lot of options to do so. In this article, I will show you how to remove rows using Power Query.

Key Takeaways

• Power Query makes it easy to remove unwanted rows.
• You can quickly remove the top and bottom rows.
• Remove Alternate Rows deletes rows in a repeating pattern.
• Power Query records each data cleaning step automatically.
• Removed rows stay excluded when you refresh your data.

 

Why Remove Rows Using Power Query?

You can manually delete unwanted rows from an Excel worksheet, but this can take time when working with large datasets. You may also need to repeat the same steps every time your data is updated.

Power Query makes this process easier by saving each row removal step. When you add new data and refresh the query, the same steps are applied again automatically. This helps me clean my data faster and keeps the process consistent.

 

Our Source Table

Here is our source table that we want to modify:

How to Remove Rows Using Power Query

STEP 1: Our sample data contains the Sales numbers for each month. Let us first prepare to use this data in Power Query.

Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:

Excel 2013 & 2010:

 

STEP 2: This will open up the Power Query Editor.

If you go to Home > Reduce Rows > Remove Rows

There are a lot of options! Let us see these 3 in action:

• Remove Top Rows
• Remove Bottom Rows
• Remove Alternate Rows

 

STEP 3: Go to Home > Reduce Rows > Remove Rows > Remove Top Rows

Let us try to remove the first two rows. Input 2 as the number of rows. Click OK.

You can see January and February are now removed.

 

STEP 4: Go to Home > Reduce Rows > Remove Rows > Remove Bottom Rows

Let us try to remove the bottom two rows. Input 2 as the number of rows. Click OK.

You can see November and December are now removed.

 

STEP 5: Go to Home > Reduce Rows > Remove Rows > Remove Alternate Rows

This is a bit trickier to use. Let us say we want to remove the following months: 3 (March), 6 (June) and 9 (September).

To do that, let us input the following:

• First row to remove – 1
• Number of rows to remove – 1
• Number of rows to keep – 2
• Click OK.

This will remove starting from the first row, then the pattern is that row will be removed, then skip the next 2 rows, then do the same pattern again. Essentially it means removing the first row of every 3 rows.

You can see March, June, and September are now removed.

 

STEP 6: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the new data.

 

You now have your new Excel table with the cleaned-up rows!

Frequently Asked Questions

How to remove blank rows using Power Query?

To remove blank rows using Power Query,

• Select the column
• Go to the Home tab
• Select Remove Rows > Remove Blank Rows to delete all empty rows.

How to remove rows based on a condition?

You can use the Filter option in Power Query to exclude rows based on specific criteria. The criteria can be values, text, or date conditions.

How to remove duplicate rows in Power Query?

To remove duplicate rows in Power Query,

• Select the relevant columns
• Go to Home > Remove Duplicates

This will remove the duplicate rows and keep the original ones.

How to delete the first or last few rows in Power Query?

To delete the first or last few rows in Power Query,

• Go to Home > Remove Rows
• Choose Remove Top Rows or Remove Bottom Rows
• Specify how many rows to delete

Will my changes stay if I refresh the Power Query?

Yes! Power Query saves all steps, so when you refresh, Excel will apply the same row removal rules to updated data automatically.