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.

6 Comments on “Power Pivot – Drill down to more than the first 1,000 rows

  1. Hi, I’m using Microsoft Office 365 ProPlus, and the option to change the maximum number is greyed out. Any workaround, please? Thanks in advance.

    1. Hi Cess, struggling to figure this out but I don’t think your Office version is the problem so I think it’s likely something else to do with the data connection. Using 365 the menu differs slightly, from Data click on ‘Queries & Connections’ then in the new box on the right right-click the connection which your pivot table is based on (in my case ‘ThisWorkbookDataModel’) and within properties the OLAP Drill Through option is not greyed out when I attempt this. If you’re following those steps and it’s still greyed out, I’d perhaps create a new Excel file from scratch, connect to the same data and try to update the drill through property. At least if that works you can troubleshoot the problem down to something file-specific rather than program-specific. Good luck!

  2. Thanks, this is something I’d been trying to figure out for a LONG time! I also had the issue with excel 2013 and the 2010 workaround worked a treat.

Leave a Reply

Your email address will not be published. Required fields are marked *