export data to excel not working in Directquery

[CAG]

Hello,

are there any known issues with exporting data into Excel or CSV from Power BI Service when being connected via Directquery?

Background:

I wanted to export some data into Excel from a table visual in Power BI Service. I expected to get around 80k rows, in fact I only got 11k. It got even more confusing after I tried a few tests:

- When only extracting one column, I get 85k rows.

- When adding more 2 more rows, data is being split up and I get 103k rows. All fine so far.

- Now I added multiple more columns to it, after 3 more columns I did a download and received 53k rows, after the rest of the columns only 11k. I am not aware of any scenario in which splitting up data gets me less results..

I then tested this in the import mode. I uploaded exactly the same table and I received all rows as expected.

Can anyone help?

Cheers

[v-qiuyu-msft]

Hi @CAG,

1. In Power BI service, export data have two options: Summarized data, Underlying data. Check if you have specified any aggregate function for the column in each table visual. Make sure each time select the same export option.

2. You can export data in Power BI desktop to see if the same issue occurs.

Best Regards,
QiuyunYu

[CAG]

Hi @v-qiuyu-msft,

1) I dont have any aggregate functions active in that table. Also, if I would miss certain data (e.g. specific keys were simply not shown any more), that would be very strange.

2) when I try to export the data in PowerBI Desktop I get an error message saying that the dataset would be too large (should be around 100k rows). Is there a stronger limitation in the Desktop version compared to the service?

Best,

CAG

[CAG]

Dear @v-qiuyu-msft,

do you have any other idea?

Thanks ahead.

[v-qiuyu-msft]

Hi @CAG,

1. I have found a issue reported internally regarding the export rows are less when using Direct Query for Power BI Service: CRI 44991248. Please see below response from PG team:

On-Prem has a pre-built limit of 32 Mbs for a Direct Query connection when trying to export data.

This 32 Mb is for the size of the table or tables used to bring in the data.

This limit was set about a year and a half ago without plans to increase it or add pop-up notifications to the customer.

2. Regarding the export data issue in Power BI desktop, I will contact with PG team internally and update here if I get information.

Best Regards,
QiuyunYu

[CAG]

Hi @v-qiuyu-msft

what should happen if the data exceeds the limit of 32mbs (which equals 4MB if I am correct)?

The download that should be around 80k rows and actually just has 11,4k rows has a size of 1,477 MB, so is less than the limit of 4 MB.

Best,

Chris

[v-qiuyu-msft]

Hi @CAG,

From this article ( https://powerbi.microsoft.com/en-us/documentation/powerbi-service-export-data/#limitations-and-consi... ) listed the export data limitations:

• The maximum number of rows that can be exported to .csv is 30,000.
• The maximum number of rows that can be exported to .xlsx is 150,000.

One thing that I would like to point out is that export to Excel <xlsx> is not supported in desktop.
So you are comparing the limitation of export to excel in service vs. export to csv in desktop.

Besides, "This 32 Mb is for the size of the table or tables used to bring in the data."

Best Regards,
QiuyunYu

[CAG]

Hi @v-qiuyu-msft,

The limit of the 32 Mb(s?) is not mentioned in the limitations over there. Perhaps it might be worth adding it to the list so other users are aware as well?

Can you confirm that the data size is limited to 32 Mbs, not 32Mb? That makes quite a major difference, since 32 Mbs are just 4 Mb.

Many thanks ahead,

Chris

[Anonymous]

Hello,

I still have issue to export data from Power BI service in Direct Query mode.

The link above i snot working.

Can some one has any update on this??

Appreciate any help.

Thanks,

Soumya

[Anonymous]

Hi,

The information given on this topic is not conclusive, can someone give an update on this?

Thanks!

Yeshela