Working around the 1000 row drillthrough limit in Powerpivot for Excel 2013

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.

 

properties dialog disabled

 

 

 

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:

 

Fruits table

 

and select “Create Linked Table” from the Powerpivot ribbon group:

 

create linked table

 

we get a table in Powerpivot:

 

Powerpivot Table

 

Still in the Powerpivot window, we create a Pivot table:

 

Create Pivot Table

 

It doesn’t matter where we put the table, we won’t be keeping it for long.

 

pivot with conns dialog

 

 

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.

 

conns dialog

 

 

Now, when we double-click on our pivot table to drill through, we get the new, higher limit:

 

max rows showing

 

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

 

enable content

 

Next we head for the Powerpivot window:

 

manage pp

 

where we’re confronted with another warning:

 

okupgrade

 

 

We click “OK” twice, then “Yes” to reopen the workbook.

 

 

yesreopen

 

Now we can go to the Powerpivot window and delete the “Fruits” table.

 

delete fruits

 

All that remains is to import our “real” data. I’ve created a dummy SQL Server table with one attribute and one measure.

 

import real data 1

 

and here it is in the data model.

 

import real data 2

 

 

Creating the “real” pivot table:

Finally, we’re ready to create our “real” pivot table.

 

realpivottable

 

 

and when we double-click to drill through on this pivot table, the limit of 1048576 rows is carried over from Excel 2010:

 

 

maxrowsnew

3 thoughts on “Working around the 1000 row drillthrough limit in Powerpivot for Excel 2013

  1. The short answer is no, I don’t think that would work, as there would be nowhere for the drilldown rows to “land”. Fortunately I don’t have a Sharepoint install handy to test this right now.

  2. Definitely not possible. Been struggling with this myself. There are reasonably clever alternatives, for example, having a sheet with all rows of details which filter by the same criteria as the slicers on your dashboard, and your users can flip between the sheets where necessary. It’s not clean, but it works.

Leave a Reply