This came up at work a couple of months ago, and ended up being a discussion thread and subsequently a Connect item. This article describes a procedure for using Excel 2010 and Excel 2013 together to work around this limitation, you’ll need both of these to follow along.
In summary, the problem was that our users wanted to be able to drillthrough from Powerpivot pivot tables, without the 1000 row limit imposed by Powerpivot 2013.
In previous version of Excel this behaviour was configurable; see, for example, here, but in the case of Excel 2013, the “properties” dialog is disabled for the Powerpivot data connection.
Not so in Excel 2010, however; we can create a Powerpivot table and set this limit to whatever we like.
Suppose we start with an ordinary Excel table:
and select “Create Linked Table” from the Powerpivot ribbon group:
we get a table in Powerpivot:
Still in the Powerpivot window, we create a Pivot table:
It doesn’t matter where we put the table, we won’t be keeping it for long.
Now we can go to the connections dialog (highlighted above), and set the “Maximum number of records to retrieve” property in the OLAP Drill Through section. 1048576 is the maximum, this should be more than enough for this demonstration.
Now, when we double-click on our pivot table to drill through, we get the new, higher limit:
Now it’s safe to delete this drilldown sheet as well as the sheet with the Pivot table from our workbook. We need to leave the source data sheet for now though, otherwise we won’t be able to upgrade the workbook to 2013. We’re finished with Excel 2010 now, so we can save the workbook and close Excel.
When we open the file with Excel 2013, we get the usual security warning, which we dismiss with “Enable Content”.
Next we head for the Powerpivot window:
where we’re confronted with another warning:
We click “OK” twice, then “Yes” to reopen the workbook.
Now we can go to the Powerpivot window and delete the “Fruits” table.
All that remains is to import our “real” data. I’ve created a dummy SQL Server table with one attribute and one measure.
and here it is in the data model.
Creating the “real” pivot table:
Finally, we’re ready to create our “real” pivot table.
and when we double-click to drill through on this pivot table, the limit of 1048576 rows is carried over from Excel 2010: