cancel
Showing results for 
Search instead for 
Did you mean: 
0

Not able export all records to Excel in Direct Query Mode

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?  

 

 

Status: Delivered
Comments
Super Contributor

@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

Frequent Visitor

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

Super Contributor

@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).

 

Not able export all records to Excel in Direct Query Mode_1.jpgNot able export all records to Excel in Direct Query Mode_2.jpg 

 

Best Regards,

Herbert

Established Member
Status changed to: Delivered