Power Pivot – Drill down to more than the first 1,000 rows

When you double-click a value on a Power Pivot table to view a listing of the results, by default the pivot table will only return the first 1,000 rows.

 

To expand this limit, go to the Data tab and click on Connections. In the dialogue box, click on ‘ThisWorkbookDataModel’ and go to Properties. In the Usage tab, change ‘Maximum number of records to retrieve’ to a number of your choice (up to the Excel limit of 1,048,576).

 

 

There is a known issue with this that sometimes occurs in Excel 2013, where the option to change the maximum number is greyed out. If you can access the workbook using either Excel 2010 or 2016 you can workaround this, by changing the Connection Properties using those versions of Excel and then when you re-open in 2013 the new limit is retained.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *