Here’s an interesting thing people used to Excel standard Pivot Table find surprising when they create a Pivot Table off a Power BI dataset. The usual expectation when you double click a value in your Pivot Table report is to see a new table in a new sheet pop up in Excel showing you all the underlying data aggregated to produce the value. Unfortunately, when the Pivot Table source is a Power BI dataset, there is a hard limit of 1000 rows of data displayed in the table of the underlying data.

How then can you make Excel show all the data?

Surprisingly, it is very easy. But so that you can follow along well and those who have not encountered the issue can also be able to learn of it and the solution, I will start with a demonstration of this issue and then show the fix.

I have some datasets and reports in my Power BI account marked as certified and exposed to Excel as usable datasets.

When I go into Data menu in my Excel desktop application, I am able to see this dataset and its tables as usable data source via the Power BI datasets option. See the screenshot below.

After clicking on Insert PivotTable, a new sheet with Pivot Table is created. I then proceed to create a report I need but want to see the underlying data.

I double click on the Equity 0.15% price change value to the see the underlying data records.

Instead of seeing all the data, I am presented with just the first 1000 rows. How do I make Excel show all the data records?

Quite easy! Go to Table menu, Properties and Connection properties as shown in the screenshot below.

In the connection properties window, go to Definition tab and delete MAXROWS 1000 in the Command text as shown in the screenshot below.

With that small change, all is done. Now the table will display all the data (if still under the Excel rows max).

And that’s it!

Don’t forget to sign up for our weekly free webinars and check out our training offerings.