Analyze in Excel shows only 1,000 underlying rows from Power BI dataset

[makshata96]

I am using Analyze in Excel on a Power BI dataset. When I double-click a PivotTable value to view the underlying data, Excel returns only the first 1,000 rows, even though more rows exist in the fact table.

I want to know if there is any setting or workaround to return all underlying rows instead of being limited to 1,000.

If anyone has faced this issue, please guide me on how to solve it

[makshata96 · Accepted solution]

Hi @rohit1991 

Thanks for your support. I really appreciate your efforts.

In Excel, there is an option to increase the row limit, but it has to be set every time. That is why I wanted to check if there is any setting in Power BI or Power Query where this limit can be configured permanently.

[rohit1991]

Hii @makshata96 

Analyze in Excel uses Power BI’s semantic model, and drill-through (Show Details) queries are limited to 1,000 rows by design. There is no setting in Excel, Power BI, or Premium capacity to increase this limit. If full underlying data is required, use Power BI export options, Paginated Reports, or query the source system directly instead of Analyze in Excel.

[makshata96 · Accepted solution]

Hi @rohit1991 

Thanks for your support. I really appreciate your efforts.

In Excel, there is an option to increase the row limit, but it has to be set every time. That is why I wanted to check if there is any setting in Power BI or Power Query where this limit can be configured permanently.

[rohit1991 · Accepted solution]

Hii @makshata96 

The Excel option you are referring to only changes the local Excel connection behavior, but it does not override the Power BI service limit. When using Analyze in Excel, Excel sends a DRILLTHROUGH MDX query to the Power BI semantic model, and the service enforces a hard 1,000-row limit on that query. Even if you increase the row limit in Excel connection properties, Power BI still returns only the first 1,000 rows. There is no permanent configuration in Power BI, Power Query, or Premium capacity to change this behavior. To retrieve more than 1,000 rows, you must use Export data, Paginated Reports, or connect directly to the source instead of Analyze in Excel.