Filter a table power query

In Power Query, you can include or exclude rows based on a column value. A filtered column contains a small filter icon ( ) in the column header. If you want to remove one or more column filters for a fresh start, for each column select the down arrow  next to the column, and then select Clear filter.

Use the AutoFilter feature to find, show, or hide values and to more easily specify filter criteria. By default, you only see the first 1,000 distinct values. If a message states the filter list may be incomplete, select Load more. Depending on the amount of data, you may see this message more than once.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select the the down arrow next to a column that you want to filter.

3. Clear the (Select All) checkbox to unselect all columns.

4. Select the check box of the column values by which you want to filter, and then select OK.

You can filter by a specific text value using the Text Filters submenu.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select the down arrow  next to the column containing a text value by which you want to filter.

3. Select Text Filters, and then select an equality type name of Equals, Does Not Equal, Begins With, Does Not Begin With, Ends With, Does Not End With, Contains, and Does Not Contain.

4. In the Filter Rows dialog box:

   • Use Basic mode to enter or update up two operators and values.

   • Use Advanced mode to enter or update more than two clauses, comparisons, columns, operators, and values.

5. Select OK.

You can filter by a number value using the Number Filters submenu.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select the down arrow of the column containing a number value by which you want to filter.

3. Select Number Filters, and then select an equality type name of Equals, Does Not Equal, Greater Than, Greater Than or Equal To, Less Than, Less Than Or Equal To, or Between.

4. In the Filter Rows dialog box:

   • Use Basic mode to enter or update up two operators and values.

   • Use Advanced mode to enter or update more than two clauses, comparisons, columns, operators, and values.

5. Select OK.

You can filter by date/time value using the Date/Time Filters submenu.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select the down arrow of the column containing a date/time value by which you want to filter.

3. Select Date/Time Filters, and then select an equality type name of Equals, Before, After, Between, In the Next, In the Previous, Is Earliest, Is Latest, Is Not Earliest, Is Not Latest, and Custom Filter.
   
   Tip    You may find it easier to use the predefined filters by selecting Year, Quarter, Month, Week, Day, Hour, Minute, and Second. These commands work right away.

4. In the Filter Rows dialog box:

   • Use Basic mode to enter or update up two operators and values.

   • Use Advanced mode to enter or update more than two clauses, comparisons, columns, operators, and values.

5. Select OK.

To filter multiple columns, filter a first column, and then repeat a  column filter for each additional column.

In the formula bar example that follows, the function Table.SelectRows returns a query filtered by State and Year.

A null or blank value occurs when a cell has nothing in it. There are two methods to remove null or blank values:

Use the AutoFilter

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select the the down arrow next to a column that you want to filter.

3. Clear the (Select All) checkbox to deselect all columns.

4. Select Remove empty then select OK.

This method examines each value in a column using this formula (for the column "Name"):

Table.SelectRows(#"Changed Type", each ([Name] <> null and [Name] <> ""))

Use the Remove Blank Rows command

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Data > Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Remove Rows > Remove Blank Rows.

To clear this filter, delete the corresponding step under Applied Steps in the Query Settings.

This method examines the entire row as a record using this formula:

Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

Filtering rows by position is similar to filtering rows by value, except that rows are included or excluded based on their position in the query data instead of by values.

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 specifying rows. For more information, see Add an index column.

To keep top rows

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Keep Rows > Keep Top Rows.

3. In the Keep Top Rows dialog box, enter a number in Number of rows.

4. Select OK.

To keep bottom rows

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Keep Rows > Keep Bottom Rows.

3. In the Keep Bottom Rows dialog box, enter a number in Number of rows.

4. Select OK.

To keep a range of rows

Sometimes, a table of data is derived from a report with a fixed layout. For example, the first five rows are a report header, followed by seven rows of data, and then followed by a varied number of rows containing comments. But you only want to keep the rows of data.

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Data > Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Keep Rows > Keep Range of Rows.

3. In the Keep Range of Rows dialog box, enter numbers in First row and Number of rows. To follow the example, enter six as the first row and seven as the number of rows.

4. Select OK.

To remove top rows

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Data > Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Remove Rows > Remove Top Rows.

3. In the Remove Top Rows dialog box, enter a number in the Number of rows.

4. Select OK.

To remove bottom rows

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Data > Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Remove Rows > Remove Bottom Rows.

3. In the Remove Bottom Rows dialog box, enter a number in the Number of rows.

4. Select OK.

You can filter by alternate rows and you can even define the alternate row pattern. For example, your table has a comment row after each data row. You want to keep the odd rows (1, 3, 5, and so on), but remove the even rows (2, 4, 6, and so on).

1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Data > Query > Edit. For more information see Create, load, or edit a query in Excel.

2. Select Home > Remove Rows > Remove Alternate Rows.

3. In the Remove Alternate Rows dialog box, enter the following:

   • First row to remove     Start counting at this row. If you enter 2, the first row is kept but the second row is removed.

   •  Number of rows to remove    Define the beginning of the pattern. If you enter 1, one row is removed at a time.

   •  Number of rows to keep    Define the end of the pattern. If you enter 1, continue the pattern with the next row, which is the third row.

4. Select OK.

Result   

Power Query has a pattern to follow for all rows. In this example, odd rows are removed and even rows are kept.

See Also

Power Query for Excel Help

Remove or keep rows with errors

Keep or remove duplicate rows

Filter by row position (docs.com)

Filter by values (docs.com)