Filter a table

Filtering rows allows you to include or exclude rows based upon a value. When a column is filtered a small filter icon (Filter icon ) appears in the column header. You can view and copy error details inside the Filter Column menu. This is useful to troubleshoot errors while retrieving filter values. You filter a table in the Query Editor.

ShowLearn more about the Query Editor

You can access the Query Editor from the Navigator pane when connecting to a data source, or at any time from the Workbook Queries pane or the contextual Query ribbon tab for an existing query. To learn how to load the Query Editor, see Power Query Quick Start.

With Microsoft Power Query for Excel, you can filter a column using several methods:

I want to


Filter a column using an Auto Filter

  1. Select the column you want to filter on.
  2. Click the down arrow (Down arrow ).
  3. Deselect (Select All) all columns.
  4. Select the column values you want included in your table.
  5. Click OK.

Select a column

 Note    Only the top 1000 distinct values in the column are loaded into the filter list when you filter a column. If there are 1000 or more values in the column in Query Editor that you are filtering, a message is displayed stating that the list of values in the filter list may be incomplete, and the Load more link becomes available. Click the Load more link to load top 1000 distinct values.

  • If exactly 1000 distinct values are found again, the list is displayed with a message stating that the list could still be incomplete.
  • If less than 1000 distinct values are found, the full list of values is displayed.

Top of Page Top of Page

Filter a column using Text Filters

In addition to the To filter a column step, you can filter a Text value using the Text Filters context menu.

  1. Click the down arrow (Down arrow ) of the column containing a Text value you want to filter on.
  2. Click Text Filters, and click an equality type name of Equals, Does Not Equal, Begins With, Ends With, Contains, or Does Not Contain.

Top of Page Top of Page

Filter a column using Number or Date/Time Filters

In addition to the To filter a column step, you can filter a Number or Date/Time value using the Number Filters or Date/Time Filters menu.

To filter a column using Number Filters or Date/Time

  1. Click the down arrow (Down arrow ) of the column containing a Number or Date/Time value you want to filter on.
  2. Click Number Filters or Date/Time Filters, and an equality type name of Equals, Does Not Equal, Greater Than, Greater Than or Equal To, Less Than, or Less Than Or Equal To.

Top of Page Top of Page

Filter multiple columns

To filter multiple columns, select an additional column, and repeat one of the column filter steps.

For example, the Table.SelectRows() formula below returns a query filtered by State and Year.

Filter result

Top of Page Top of Page

Filter a column 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.

With Microsoft Power Query for Excel, you can filter a column by position using several methods:

 Note    When you specify a range or pattern, the first data row in a table is row zero (0), not row one (1). You can create an Index column to display the row positions prior to removing rows.

Keep Top Rows

  1. Right-click the table icon (Table icon ).
  2. Click Keep Top Rows.
  3. In the Keep Top Rows dialog box, enter the Number of rows.

Keep Top 100 Rows

  1. Right-click the table icon (Table icon ).
  2. Click Keep Top 100 Rows.

Keep Range of Rows

  1. Right-click the table icon (Table icon ).
  2. Click Keep Range of Rows.
  3. To define your range, in the Keep Range of Rows dialog box, enter the First row and Number of rows.

Remove Top Rows

  1. Right-click the table icon (Table icon ).
  2. Click Remove Top Rows.
  3. In the Remove Top Rows dialog box, enter the Number of rows.

Remove Alternate Rows

  1. Right-click the table icon (Table icon ).
  2. Click Remove Alternate Rows.
  3. To define your alternate row pattern, in the Remove Alternate Rows dialog box, enter the First row to remove, Number of rows to remove, and Number of rows to keep.

Top of Page Top of Page

Related Topics

Remove rows with errors

 
 
Applies to:
Excel 2013, Excel 2010