# Excel 2013 powerpivot drilldown is limited to 1000 rows, connection can not be edited anymore



## renz

Excel 2013 ProPlus Powerpivot drilldowns are limited to 1000 rows.

In Excel 2010 this number could be increased by editing connection properties.
In Excel 2013 this is not possible anymore, option in the connection is grayed out with the statment "not all options can be modified because this connection was generated by the PowerPivot plugin".

Is there another way to increase drilldown maxrows to beyond 1000 rows ?


----------



## Andrew Poulsom

Are you saying that the option wasn't greyed ot when using PowerPivot in Excel 2010? I thought the AutoFilter list limit in Excel 207 and above was 10,000 items.


----------



## renz

Hi, this does not concern the autofilter list limit. I'm talking about the Powerpivot Drilldown function. This is a feature that is activated when you double click on a Powerpivot data field, in that case an extra worksheet appears showing the detail records that belong to the data field you clicked on. In a "normal" pivot you would just get all the relevant detail records. In Powerpivot this is limited to the first 1000 records. But where Exel 2010 had a workaround for this limit (editing the connection properties), Excel 2013 doesn't (at least I can't find it, the relevant connection properties are greyed out). This makes Excel 2013 Powerpivot rather useless for now...


----------



## miguel.escobar

Hey Renz,

I didn't wanted to respond to this thread because I knew that this feature wasn't available (To drill down pass the 1000 rows) but I'm still looking for a workaround and trying to understand more about how the drill down operation works.

Hopefully other 2013 users would give us some pointers on what they've done to get over 1K records when drilling down.


----------



## renz

Contacted Microsoft and they are looking into this problem.
Meanwhile found a workaround. If you open the workbook containing the powerpivot in Excel 2010 you can still edit the number of returned rows in the connection properties. Save and close. Now open the workbook in Excel 2013 and the connection properties are still greyed out, but with the new record limit retained.


----------



## dceralde

Actually you can go well beyond the 1k limit. As a matter of fact you can go to 1,048,576 rows. The trick is
1) Place your mouse OUTSIDE of a pivot table
2) Click the DATA menu
3) Click Connections
4) In the Workbook Connections dialogue box click "ThisWorkbookDataModel" (at least that's what mine says)
5) Click Properties.  In the Usage tab, “Maximum number of records to retrieve” should not be grey out.

I hope this helps.


----------



## miguel.escobar

Awesome! I wonder if that's something that Excel picked up with the last stability fix:
Announcing New Excel 2013 BI Stability and Usability Improvements - PowerBI - Site Home - MSDN Blogs

I just tried it myself and it works just like in Excel 2010. No more greyed out situations! No need to be outside the Pivot table tho.


----------



## johnstockett

I registered for this forum just to express my gratitude to dceralde.  Your answer was EXACTLY the help I needed.  Thanks a 1,000,000 (because thats what I needed to set my new connection to for a very large project I'm working on)!




dceralde said:


> Actually you can go well beyond the 1k limit. As a matter of fact you can go to 1,048,576 rows. The trick is
> 1) Place your mouse OUTSIDE of a pivot table
> 2) Click the DATA menu
> 3) Click Connections
> 4) In the Workbook Connections dialogue box click "ThisWorkbookDataModel" (at least that's what mine says)
> 5) Click Properties.  In the Usage tab, “Maximum number of records to retrieve” should not be grey out.
> 
> I hope this helps.


----------



## smartnaga

dceralde said:


> Actually you can go well beyond the 1k limit. As a matter of fact you can go to 1,048,576 rows. The trick is
> 1) Place your mouse OUTSIDE of a pivot table
> 2) Click the DATA menu
> 3) Click Connections
> 4) In the Workbook Connections dialogue box click "ThisWorkbookDataModel" (at least that's what mine says)
> 5) Click Properties.  In the Usage tab, “Maximum number of records to retrieve” should not be grey out.
> 
> I hope this helps.




-----Thanks a lot


----------



## Bob_W

dceralde said:


> Actually you can go well beyond the 1k limit. As a matter of fact you can go to 1,048,576 rows. The trick is
> 1) Place your mouse OUTSIDE of a pivot table
> 2) Click the DATA menu
> 3) Click Connections
> 4) In the Workbook Connections dialogue box click "ThisWorkbookDataModel" (at least that's what mine says)
> 5) Click Properties.  In the Usage tab, “Maximum number of records to retrieve” should not be grey out.
> 
> I hope this helps.



Holy thread revival Batman!! Sorry .... but I need some help 

I've followed the guidance above and get the following error message "This connection name is already in use. Try a different name."

Any idea what I'm doing wrong?


----------



## renz

Excel 2013 ProPlus Powerpivot drilldowns are limited to 1000 rows.

In Excel 2010 this number could be increased by editing connection properties.
In Excel 2013 this is not possible anymore, option in the connection is grayed out with the statment "not all options can be modified because this connection was generated by the PowerPivot plugin".

Is there another way to increase drilldown maxrows to beyond 1000 rows ?


----------



## mattymc84

Bob_W said:


> Holy thread revival Batman!! Sorry .... but I need some help
> 
> I've followed the guidance above and get the following error message "This connection name is already in use. Try a different name."
> 
> Any idea what I'm doing wrong?



Likewise!  it's not greyed out, but I get the same error message as you.


----------



## lmorales

dceralde said:


> Actually you can go well beyond the 1k limit. As a matter of fact you can go to 1,048,576 rows. The trick is
> 1) Place your mouse OUTSIDE of a pivot table
> 2) Click the DATA menu
> 3) Click Connections
> 4) In the Workbook Connections dialogue box click "ThisWorkbookDataModel" (at least that's what mine says)
> 5) Click Properties. In the Usage tab, “Maximum number of records to retrieve” should not be grey out.
> 
> I hope this helps.



You rock!


----------

