Work Around the 1,000-Row Power Pivot Drill-Down Limit in Excel 2016
Question details
The user needs to retrieve more than 1,000 underlying records for an Excel 2016 PivotTable value.

- Product
- Microsoft Excel 2016 Power Pivot
- Device & OS
- not provided
- Scenario
- The PivotTable uses worksheet data from another workbook, and the user wants to inspect all records behind a summarized value.
- Observed behavior
- Drilling through returns no more than 1,000 records, and the OLAP maximum-records setting is disabled.
Locate the source workbook and identify the PivotTable value and filters you want to investigate. Check which source columns correspond to the PivotTable's row, column, and report filters.
Use Power Query to retrieve the matching source records
Use this workaround to load and filter the source workbook independently of the PivotTable drill-through limit.
The OLAP maximum-records setting is unavailable for the worksheet source described here. Filtering the source with Power Query does not change that setting; it creates a separate result.
Match the PivotTable filters as closely as possible. Calculated measures, relationships, and other data-model logic may require additional filters or transformations.
In Excel 2016, select Data > New Query > From File > From Workbook, then choose the workbook containing the source data.
In Navigator, select the relevant worksheet or table and choose Edit to open Power Query Editor.
Use the filter buttons in the column headers to select the dates, categories, or other values represented by the PivotTable cell.
Select Home > Close & Load to load the filtered records into Excel. Use Data > Refresh All when you need to update the result.

Filter the source worksheet directly
Use this option when you can open the source workbook and identify the records represented by the PivotTable value.
Analyze spreadsheet data with WPS Office
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue everyday analysis as you move between office suites. It does not change Excel's Power Pivot drill-through setting.

Frequently Asked Questions
Why is the maximum number of records setting disabled?
The OLAP Drill Through maximum-records setting applies to OLAP data sources. It cannot be edited for the worksheet source described in this case.
Can Power Query return more than 1,000 matching records?
Yes. In Excel 2016, use Data > New Query > From File > From Workbook, filter the source data, and load the result. The number loaded is still subject to Excel's worksheet capacity.
Will filtering the source always reproduce the PivotTable drill-down?
Not necessarily. Apply the same visible PivotTable filters to the source, but also account for any data-model relationships, calculated measures, or other logic that affects the value.
Would moving the data to an OLAP source help?
An OLAP-compatible source can make the OLAP drill-through maximum-records setting available. The result also depends on the source and its configuration; moving data requires setting up an appropriate OLAP data source.




