Excel Power Query: How to Keep Rows

In Excel Power Query, select the rows you want to retain in Power Query Editor, then use Home > Keep Rows for options such as keeping the top rows, a range, duplicates, or errors. To keep rows that meet a condition, use the filter on a column instead; the choice depends on whether you mean a position-based selection or a rule-based one.

What Keep Rows does

Power Query is Excel’s tool for importing, cleaning, and reshaping data. Its Keep Rows commands add a step to a query that limits which records continue through the transformation. The original source is not deleted or edited by that step. When you load the query result, Excel displays the rows that remain after the applied steps.

The important distinction is that “keep” can describe two different tasks:

Keep rows by position or pattern: retain the first few rows, a particular range, or alternating rows.
Keep rows by contents: retain rows matching a filter, containing duplicates, or containing errors.

For example, keeping the top 10 rows means retaining the first 10 rows in the query’s current order. Keeping rows where Region equals West means retaining records that satisfy a value condition, wherever they appear. These are different operations, even though both reduce the output.

Use the Keep Rows menu

In Excel for Microsoft 365 on Windows, open the query in Power Query Editor. On the Home tab, find Keep Rows in the Reduce Rows group. The menu offers several operations, though the exact arrangement can vary with the editor window size or build. Choose the option that matches the selection you need.

Keep Top Rows retains a specified number of rows from the beginning of the current order.
Keep Bottom Rows retains the specified number from the end.
Keep Range of Rows retains a consecutive block, based on a starting row and a count.
Keep Alternate Rows retains a repeating pattern of rows, using settings for where to begin, how many to keep, and how many to skip.
Keep Duplicates retains rows whose values are duplicated in the selected column or columns.
Keep Errors retains rows with errors in the selected column or columns.

Most positional options open a dialog for a number or range. Enter the requested values and confirm. Power Query adds a step to Applied Steps; selecting that step lets you see the resulting preview. If you choose the wrong option, remove or edit the step rather than changing the source workbook.

Keep rows that meet a condition

For conditions such as “keep only active customers,” use a column’s filter rather than Keep Top Rows. Select the filter arrow in the relevant column header, then choose or exclude values, or use the available text, number, date, or other filters. For example, to retain sales from the West region, open the Region filter and select West. To retain dates from a particular period, use a date filter that expresses the required interval.

Power Query records this as a filter step, commonly named Filtered Rows. You can filter on more than one column; the resulting query keeps records meeting the combined conditions. If the rule is more complex than the dropdown options allow, the filter menu may provide advanced criteria, or you can use the formula bar or Advanced Editor to adjust the M query. Make sure the column’s data type is appropriate: a date stored as text, for example, may not offer the date filtering behavior you expect.

Filters are usually the right choice for business rules because they keep matching records even when new source rows arrive on refresh. A top-10 operation does not mean “the 10 newest” unless you first sort by a reliable date or timestamp in descending order. Likewise, filtering for a status value is not the same as keeping a fixed number of rows.

Keep a particular number or range

Use Keep Top Rows when you need a sample or a fixed count. Suppose an imported file has 800 records and you want to inspect its first 25. Choose Home > Keep Rows > Keep Top Rows and enter 25. This keeps the first 25 records in the order they have at that point in the query. It does not automatically select the most recent, highest-value, or otherwise preferred records.

For a meaningful “latest 25” result, first sort the date or timestamp column from newest to oldest, then keep the top 25. Sorting before the keep step matters because Power Query applies steps in sequence. If an earlier step changes the order, the rows selected by a later positional step may change too. For repeatable results, sort by a secondary unique identifier as well when timestamps can tie.

Keep Range of Rows is useful when a block of records is needed—for example, rows 101 through 150 of a deliberately ordered list. Enter the first row and the number of rows requested by the dialog. Be precise about whether you are counting from the first displayed row; the interface asks for a row position, while the underlying M function uses a zero-based offset. You generally do not need to calculate that offset when using the dialog.

Keep Bottom Rows can help inspect the end of a file, but it also depends on the current order. If a source system does not guarantee row order, sort the data explicitly before keeping the bottom or top records. A query’s preview order should not be treated as a business rule unless you have established that order.

Keep duplicates or errors

Keep Duplicates is useful when you want to investigate repeated records rather than remove them. Select the column or columns that define what counts as a duplicate, then choose Home > Keep Rows > Keep Duplicates. If you select only Customer ID, rows sharing an ID are considered duplicates for that selection, even if their names or dates differ. If you select Customer ID and Order ID, the combination determines which records are repeated.

This is not the same as Remove Duplicates. Keep Duplicates returns records identified as having repeated selected values; Remove Duplicates removes repeated combinations and retains a representative row. If you need one record per customer, decide which record should represent the customer and sort or otherwise define that rule before removing duplicates. Do not assume the retained representative is necessarily the newest or best record.

Keep Errors is a diagnostic tool. Select the column or columns to check, then use the Keep Errors command to isolate rows with errors there. This can reveal conversion failures, invalid dates, or calculations that cannot be evaluated. It does not fix the errors. After examining the cause, you can correct the transformation, replace errors, or remove error rows if excluding them is appropriate. Removing them without checking may conceal missing or malformed source data.

How the query step works

Power Query stores transformations as steps and evaluates them in order. A keep operation changes the rows passed to later steps; it does not usually change the structure of the source file. You can inspect the step list in Query Settings and select earlier steps to see how the preview changes. Renaming steps with descriptive names, such as Kept Latest 25 after a sort, makes a multi-step query easier to understand.

Under the hood, positional operations can use M functions such as Table.FirstN, Table.LastN, or Table.Range. A filter is commonly represented by Table.SelectRows. The generated expression refers to the prior step, so it is usually safer to create the operation through the interface than to type a full expression without knowing the query’s step names and table structure.

For example, Table.FirstN(#"Previous Step", 10) represents taking 10 rows from the table produced by a step named Previous Step. It does not sort the table first. The expression’s outcome therefore depends on the order supplied by the preceding query. Applied Steps are not a list of independent actions: deleting or reordering a step can affect every step after it.

Refresh behavior and loading results

After shaping the query, choose Home > Close & Load to load its result to a worksheet or the Data Model, depending on the query’s load settings. Later, use Data > Refresh All, or refresh the specific query from the Queries & Connections pane, to run it again against its source. A positional keep step is recalculated on refresh, so it keeps the specified number or range from the refreshed query’s current order.

That means a fixed-count query can return different records after the source changes. If the source gains new rows at the top, Keep Top Rows may select a different set. If a filter keeps Region = West, newly added West records can appear on refresh, provided they are included in the source and pass the other steps. Check the source range or connection as well as the query steps if refreshed data seems incomplete.

Power Query is available in current Microsoft 365 versions of Excel for Mac, but menus and capabilities can differ by platform and update. The instructions above use Excel for Microsoft 365 on Windows. Excel for the web may allow some workbook and query refresh workflows, but the full Power Query editing experience is not the same as the Windows desktop editor; use the desktop app when you need the complete set of authoring controls.

Common problems and how to avoid them

The wrong records remain: Check the sort and filter steps before the keep step. A positional command acts on the order at its place in the sequence.
There are fewer rows than expected: An earlier filter or error-removal step may already have excluded records. Select earlier Applied Steps to inspect the intermediate result.
Duplicates are not identified as expected: Check which columns were selected when Keep Duplicates was applied. A duplicate is defined by the selected values, not necessarily by every field in the row.
A filter option is missing or behaves strangely: Check the column’s data type and whether the preview contains the values you expect. Text and numeric representations of similar-looking values can behave differently.
The result changes after refresh: Confirm whether the source order is stable and whether a preceding sort is required. Also confirm that the source range or connection includes newly added data.

One common misconception is that keeping rows permanently deletes the others. The transformation limits the query output; it does not remove those records from the original workbook, database, or file. Another is that “keep top” means “keep best.” Power Query has no basis for deciding what is best: you must define the order with a sort or express the desired rule with filters.

Frequently Asked Questions

How do I keep only rows with a certain value in Power Query?

Open the filter dropdown in the relevant column, select the value or define a condition, and confirm. Power Query adds a filter step that retains matching rows, including new matching records when the query refreshes.

How do I keep the first 10 rows?

In Power Query Editor, choose Home > Keep Rows > Keep Top Rows and enter 10. Sort the table first if you mean the 10 highest, newest, or otherwise ranked records.

What is the difference between Keep Duplicates and Remove Duplicates?

Keep Duplicates isolates rows whose selected column values repeat. Remove Duplicates discards repeated combinations and keeps a representative row. Choose columns carefully, because they define what counts as a duplicate.

Can I undo a Keep Rows step?

Yes. In Query Settings, remove the step from Applied Steps, or use the step’s settings where available. This changes the query transformation, not the original source data.

Why does Keep Top Rows return different records after refresh?

The step keeps the first specified number in the order available when the query runs. If source order changes, or new rows are inserted before existing ones, the selected records can change. Sort explicitly before keeping rows when the selection must follow a rule.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.