Excel Table from Power BI Dataset Limited to 500,000 Rows

[lilianarondon]

Hi Power BI Community,

I’m using Excel connected to a Power BI dataset (Analyze in Excel feature) to create a table. My dataset in Power BI contains more than 500,000 rows, but in Excel, the table only shows up to 500,000 rows—the rest are missing.

Is this a limitation of the Analyze in Excel feature? If so, what are the recommended workarounds to export the full dataset?

Here’s what I’ve tried:

• Verified the dataset in Power BI (it has more than 500,000 rows).
• Checked Excel for filters or hidden rows.

Any guidance on how to get all rows into Excel or another tool would be greatly appreciated!

Thanks,
Liliana Rondon

[tayloramy · Accepted solution]

@lilianarondon, 

You can in theory modify the DAX/MDX query to increase the row count, though this could have some pretty large impacts on your capacity. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[tayloramy · Accepted solution]

Hi @lilianarondon, 

From a blank excel sheet, get data from PowerBI: 

then select your dataset: 

Inserting a PivotTable will use MDX queries (highly inefficient for Fabric as the capacity computes the entire model every time you do anything) 

You can now go to Data -> Queries and connections 

select your connection, and then set the max records to retrieve: 

Inserting a table will use DAX (better for Fabric, I would recommend inserting all the tables and then making a pivot yourself rather than using the MDX pivot)
For the DAX query, you can do the same thing, but you need to modify the query in the definition tab once you get to the connection properties: 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[lilianarondon]

I will try today it seems it will work.

Thank you for your support, it was very helpfull. 

[lilianarondon]

Hello Taylor, How I can modify the DAX/MDX query?

[tayloramy · Accepted solution]

@lilianarondon, 

You can in theory modify the DAX/MDX query to increase the row count, though this could have some pretty large impacts on your capacity. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[lilianarondon]

Hello Taylor, How I can modify the DAX/MDX query?

[KarinSzilagyi]

Hi @lilianarondon, it's a known limitation of the Analyze in Excel feature: https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-in-excel 

The Power BI Service throttles the data via the XMLA endpoint for Live Connections(which is also used for the Analzye in Excel feature). 

I never needed this myself so far, but supposedly you can export more than 500k rows if you connect your dataset to DAX Studio, run 

EVALUATE 'YourTable'

then you can export the result to CSV and then transform that into an excel if needed.

Here's a related video I found that might help you: https://www.youtube.com/watch?v=op6f-3uUFYg