Change the Rows that Are Imported from a Data Source
You can apply a filter to a data source when you import data to restrict the number of rows in your workbook. Later, if you decide to add more rows, or restrict the rows, you do not need to recreate the data with a different filter; instead, you can simply edit the filters that are applied to the data source.
The first procedure in this topic describes how to add filters when importing data to restrict the rows that are added to your workbook. The second procedure describes how to edit filters that are applied to existing data sources. Both procedures focus on importing data from a database, but the steps are similar for other data sources. The procedure assume that you will select tables from a list in the Table Import Wizard; if you choose to write a query to import data, you can filter data by specifying in the query which rows and columns to import. After data is imported, you can apply filters to control which data is displayed in the PowerPivot window. For more information, see Filter Data in a Table.
Note
In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.
Adding a Filter during Import
To define a filter during import
In the PowerPivot window, click the Home tab, and in the Get External Data group, click one of the following buttons to open the Table Import Wizard: From Database, From Report, From Data Feeds, From Text, or From Other Sources.
On the Select Tables and Views page, select a table, and then click Preview & Filter.
In the Preview Selected Table dialog box, locate the column that contains the criteria you want to filter on, and then click the down arrow at the right of the column heading.
In the AutoFilter menu, do one of the following:
In the list of column values, select or clear one or more values to filter by, and then click OK.
If the number of values is extremely large, individual items might not be shown in the list. Instead, you will see the message, "Too many items to show."
Click Number Filters or Text Filters (depending on the type of column), and then click one of the comparison operator commands (such as Equals), or click Custom Filter. In the Custom Filter dialog box, create the filter, and then click OK.
Note
If you make a mistake and need to start over, click Clear Row Filters.
Click OK to save the filter condition and return to the Select Tables and View page of the wizard.
The Filter Details column now contains a link, Applied filters. Click the link to review the filter expression that was built by the wizard. The syntax for each filter expression depends on the provider and cannot be edited manually.
Click Finish to import the data with filters applied.
Changing the Definition of a Filter on an Existing Data Source
After you have imported data, you may want to change the rows that are imported. You can do this by editing the existing filter, or by adding new filters. For data feeds, you can only change the columns that are imported; you cannot filter rows by values. However, you can filter the rows that are imported from a data feed if the source of the data feed is a report, and the report is parameterized. For more information, see Import Data from a Reporting Services Report.
To edit a filter on an existing data source
In the PowerPivot window, click the Design tab, and in the Connections group, click Existing Connections.
Select the connection that has the data you want to change, and click Open.
Edit the filtering criteria by following the steps described in the previous procedure.