Created PT using PQ. But when double clicking for data, only 1000 rows shown

[danhendo888]

I have saved three Excel files in a folder.
Used Power Query to create a Pivot table from them.
But when I double click on the grand total or subtotal, it only shows the first 1000 rows

Is there a fix to this?

FYI: OLAP Drill Down thing is greyed out

 

[GraH]

Hi, this is the normal behaviour for PowerPivot.
If you load directly in Pivot (without the checkbox Add to datamodel) it will work. I don't think you've created a datamodel. Sounds like you just combined 3 tables.

 

[Rusty_Shackleford_Null]

This worked for me.

• Change the .xlsx to .zip in the file.
• Navigate to FileName.zip -> xl -> connections.xml
• Right click and open the connections.xml file
• Use notepad to search for "rowDrillCount="1000"
• Change 1000 to your number of rows needed
• Save the connections.xml file outside the newly create FileName.zip folder
• Delete the original connections.xml file from FileName.zip
• Paste the new connections.xml to FileName.zip -> xl
• Change FileName.zip back to FileName.xlsx

 

[Mark Milan]

Got It. Thank you !
Is it possible to export the filtered data in another workbook instead of another sheet?