Filter the data you import into power pivot

When you import data from an external data source into Power Pivot, you choose which columns you want from each table, and filter to restrict the number of rows in your Data Model.

1. In Excel, Power Pivot tab > Manage Data Model to open the Power Pivot window.

2. Click Home > Get External Data, click one of these buttons to open the Table Import Wizard: From Database, From Report, From Data Service, or From Other Sources.

3. On the Select Tables and Views page, select a table, and then click Preview & Filter.

4. To exclude columns, clear the boxes next to the columns that you don't want to import.

5. To reduce rows, 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:

   1. In the list of column values, select or clear one or more values to filter by. For example, if the columns contain product category names, select only those categories you want to import. 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."

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

      Note:  If you make a mistake and need to start over, click Clear Row Filters.

6. 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 the wizard created. The syntax for each filter expression depends on the provider and cannot be edited manually. Click OK to close the Filter Details dialog box.

7. Click Finish to import the data with filters applied.

Filters that reduce the amount of imported data are applied only during import operations. You can't add filters after the data is already imported and then refresh to get smaller rowsets.

Filters that control which data is displayed in Excel or in Power Pivot are easily applied at any time. Notice that filtering an existing dataset doesn't change the data that's in the workbook—just the data that's displayed. So if file size is a concern, filtering the data as you import results in a smaller workbook.

For data feeds, you can only change the columns that are imported. You can't filter rows by values unless the source of the data feed is a report, and the report is parameterized.