Not able export all records to Excel in Direct Query Mode

[ypradeep23]

We have data (size ~100K) in On-Premise SQL Server database, and created an Power BI report in "Direct Query"  mode. Observed an interesting issue while exporting data from Table visual to excel.

Report Details:

1. Has 2 visuals... "Card" , "Table"
2. No filters on data
3. No Aggregates on data
4. Card is showing "100K"

Issue #1: when I export data from Table to excel...it is exporting only ~60K rows. verified Power Bi SQL query at Gateway using SQL Profiler... there it is clearly showing the Row count as 100K, that has returned from database.

I tried to convert report from “Direct Query” to “Import” mode. Then excel export is working as expected. But need to why it causing issues in “Direct Query” mode.

Issue #2: One more observation is that, even though there are NO Aggregates in my report … SQL query generated by Power BI is having “Group By”, which is not necessary and will cause performance hit.

Can anyone try to explain the reason for this behaviour?  

[v-haibl-msft]

@ypradeep23

Where did you export the data, in Power BI Desktop or Service? If in Desktop, there is a related issue here. If in Service, the maximum number of rows that can be exported to .xlsx is 30,000 (for Power BI Free users) and 150,000 (for Power BI Pro users).

Best Regards,

Herbert

[ypradeep23]

@v-haibl-msft Exporting data from Power Bi Serivce as Pro-user. And total data is less than 100k without any filters.

[v-haibl-msft]

@ypradeep23

I tried to get data from my SQL with direct query and I have a table with 97K rows. After publish to Power BI Service, I can export data completely as below.

If you still have this problem, I suggest you to create a support ticket at http://support.powerbi.com (see bottom of page).

 

Best Regards,

Herbert