Excel 365 Power Pivot Table Double Click shows only first 1000 rows

[Pablos_007]

Hello,

Need some help please. Trawled the internet and couldn't find a solution in Excel 365.

I have an Excel Workbook. If I click on the Data tab on the ribbon and click Queries & Connections I can see that there is 1 query under Queries and 0 connections under Connections.

The query is extracting CSV data from files in a folder. I have transformed the data and loaded as a Pivot Table and clicked on the tick box for DataModel. I have then created a single DAX measure for the Sum of a column in the Table.

If I double click a cell within my Pivot Table the data returned shows First 1000 Rows. (How to change this?)

I can't remember how to change this so it shows say 99,999 rows.

I have seen this done before. 

If I click on Properties on the Data tab I only get to see Query Properties. I think the solution lies in getting to the Connection Properties? 

So I am opening this up and hoping for assistance 🙂

[Pablos_007 · Accepted solution]

Foodd suggestion – Excel 365 in Query Editor make change at bottom left of pane. This allows you to copy all the rows and columns in the Query Editor Table and paste outside of the Editor. Unfortunately, it didn’t fix my issue.

Solution :-

In Excel, Issue if create Pivot Table from Data Model – Drill through default on Pivot Table (double click from within Pivot Table) only shows first 1000 rows.

Get around solution: In Excel 365, on the Ribbon go to Data. Then, click on Queries & Connections. Go to ‘Connections’.

Right Click to see Connections Properties.

Change the value from 1000 in OLAP Drill through to suit. Then Save.

This should then work for you.

*** The Difficulty I had I could not get the Connection Properties to show the connection. Need to see ThisWorkbookDataModel. The change needs to be made here.         I kept seeing the Query Properties.

[kkbarton]

For me, sometimes "ThisWorkbookDataModel" shows up and sometimes it does not (i.e., only the "WorksheetConnection" shows up. When I create a pivot table from another sheet in the workbook, I DO select "Add to Data Model".

[foodd]

With the Power Query Editor Open, at the bottom left area click on

where it says "Column profiling based on top 1000 rows", and change

to "Column profiling based on entire data set"   This will load all rows

the data and not just the top 1000 rows.