Excel Power Query retrieving only 1000 rows when drilled down

Question

I have created a pivot using powerquery connection in excel. If i drill down the pivot values, it retrieves only 1000 records with a message "Data returned for (First 1000 rows)"

Is there a way to extend this 1000 limit to show all the records.

Thanks Varun

Answer 1

According to this post, you can adjust the limit by opening connection properties of your data model:

This source adds that the hard maximum number of rows is 1,048,576.

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"
5. Click Properties. In the Usage tab, “Maximum number of records to retrieve”