Filter a table (Power Query)

  This article applies to Power Query, a data analysis feature available for Excel that lets you discover, combine and refine data. You may need to enable Power Query in Excel. You can also download and install the most recent version of Power Query for Excel, which automatically enables it. Power Query technology is also built into the Power BI Designer, which is a stand-alone report authoring and data transformation tool for Power BI.

Filtering rows in Power Query 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. With Power Query, you can filter a column using several methods:

I want to

Filter a column using an Auto Filter

Filter a column using Text Filters

Filter a column using Number or Date/Time Filters

Filter multiple columns

Filter a column by Row Position

Keep Top Rows

Keep Top 100 Rows

Keep Range of Rows

Remove Top Rows

Remove Alternate Rows

Related Topics

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

    Notes    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.

    Note   The Query Editor only appears when you load, edit, or create a new query using Power Query.

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

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

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

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

Related Topics

Remove rows with errors

Applies To: Excel 2010, Excel 2013



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language