locked
Power Pivot drillthrough limited to 1000 rows RRS feed

  • Question

  • Hello,

    I am using Excel 2016, I made connection to an external Excel file and used Power Pivot to analyze the data. When I double clicked on one of the results, it only returned the first 1000 rows. I followed an online article (for Excel 2013), but the option to increase the number of results is greyed out.

    Is there a new feature in 2016 to solve this?

    Thank you,

    Amy

    Wednesday, June 6, 2018 4:55 PM

All replies

  • Hi,

    Please try this method:

    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.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, June 7, 2018 7:48 AM
  • Hi, 

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, June 12, 2018 1:22 AM
  • Hi Emi, 

    The screenshot above clearly shows "Maximum number of records to retrieve" is grey out. I have the same view as above.

    How to allow this is not grey out to be able to update the number? 

    Cheers, 

    Bonie

    Thursday, January 10, 2019 12:14 AM
  • Emi, Thank you so much for posting this! This has been a thorn in my side for 1-2 months now and has alleviated a LOT of heartburn.
    Monday, March 11, 2019 2:18 PM
  • HI,

    Does anybody know how to change the 1000 drilldown limit in office 365 programatically using VBA?  how can I set this to be the default for any new workbooks.   I am using automation to produce Excel privot tables.

    Sergio Mundarain

    Tuesday, October 15, 2019 4:12 PM
  • Hi Emi,

    I am also experiencing the same issue.

    Did you get a chance to look for the solution. The screenshot you posted clearly shows "Maximum number of records to retrieve" is grey out. Any other thoughts.?

    Monday, June 8, 2020 8:48 PM
  • These steps also work perfectly and they can be done throught automation:

    1) Change the .xlsx to .zip in the file.

    2) Navigate to FileName.zip -> xl -> connections.xml

    3) Right click and open the connections.xml file

    4) Use notepad to search for "rowDrillCount="1000"

    5) Change this to "rowDrillCount="1000000"

    6) Save the connections.xml file outside the newly create FileName.zip folder

    7) Delete the orginal connections.xml file from FileName.zip

    8) Paste the new connections.xml to FileName.zip -> xl

    9) Change FileName.zip back to FileName.xlsx

    Monday, June 8, 2020 9:03 PM
  • Thanks - First time I had to edit internally an XLSX file: little cumbersome, but it finally worked.

    Saturday, July 4, 2020 6:00 PM
  • You're a hero :)

    just friendly reminder for others. Works only for xlsx and xlsm. I am using xlsb all the time, so people have to keep in mind that in this format there are uneditable bin files, instead of xml.

    Temporary save as xlsm, applying your fix and then bringing back to xlsb works just fine.

    Monday, July 20, 2020 2:25 PM