Filter by Row Position (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

In Power Query, you can use Query Editor to filter by row position. Filtering rows by position is similar to filtering rows by value, except that rows are kept or discarded by their position in the table rather than by cell values. You create an Index column to show the row positions prior to removing any rows.

To Keep Top Rows

  1. Click the table icon ( Table icon ).

  2. From the table context menu select, Keep Top Rows.

  3. In the Keep Top Rows dialog box, enter the Number of rows to keep.

  4. Click Apply.

The Power Query Query Editor

To Keep Top 100 Rows

  1. Click the table icon ( Table icon ).

  2. From the table context menu select, Keep Top 100 Rows.

To Keep Range of Rows

  1. Click the table icon ( Table icon ).

  2. From the table context menu select, Keep Range of Rows.

  3. In the Keep Range of Rows dialog box:

    1. Enter the position of the First row to keep.

    2. Enter the Number or rows to keep.

  4. Click Apply.

To Remove Top Rows

  1. Click the table icon ( Table icon ).

  2. From the table context menu select, Remove Top Rows.

  3. In the Remove Top Rows dialog box, enter the Number of rows to remove.

  4. Click Apply.

To Remove Alternative Rows

  1. Click the table icon ( Table icon ).

  2. From the table context menu select, Remove Alternative Rows.

  3. In the Remove Alternative Rows dialog box:

    1. Enter the First row to remove.

    2. Enter the Number of rows to remove.

    3. Enter the Number of rows to keep.

  4. Click Apply.

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×