Executing the query for the export data operation resulted in an error in Table visual in service

[nm17844]

Hello Everyone

While exporting data from power bi services from table visual am getting error as Executing the query for the export data operation resulted in error.

Few things to note:-

1. Using Direct Query mode for this semantic model.

2. The filter condition for which am trying to export data could see no of records <150000

3. Tried to export by reducing columns and with 5-10 columns am able to export , however actual report contains 20+ columns and user want to see all the fields.

4. checked few articles in microsoft community already although it doesn't provide any solution as such .

https://community.fabric.microsoft.com/t5/Issues/Unable-to-export-Data-with-current-layout/idi-p/267...

[FarhanJeelani · Accepted solution]

Hi @nm17844 ,

The error you're encountering during export in Power BI Service is likely due to limitations with DirectQuery mode or memory constraints. Here are some suggestions to address the issue:

1. Reduce Query Complexity: Simplify the query by reducing the number of calculated columns or measures used in the visual.

2. Aggregate Data: If possible, pre-aggregate data to reduce the volume queried at runtime.

3. Increase Column Export Limit: Adjust the MaxRows or MaxColumns settings in the Power BI Desktop export configuration (if applicable), though this may not fully resolve service-side limitations.

4. Test with Import Mode: Temporarily switch the dataset to Import mode to see if the issue persists.

5. Optimize Filters: Ensure filters applied in the visual reduce the dataset size effectively.

6. Log Support Ticket: If none of these work, consider raising a support ticket with Microsoft for assistance.

Please mark this as solution if it helps. Appreciate Kudos.

[v-hashadapu]

Hi @nm17844 , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @nm17844 , Hope your issue is solved. If it is, can you please consider marking it 'Accept as solution'. If not, please share the details. Thank you.

[v-hashadapu · Accepted solution]

Hi @nm17844 ,Thank you for reaching out to Microsoft Fabric Community Forum.

Please check the following

1. DirectQuery has inherent limitations, especially when exporting data. DirectQuery operations typically restrict the amount of data that can be exported (1 million rows in Power BI Service). When using DirectQuery, complex queries (especially those with many joins, filters, or calculations) can fail during export due to the heavy load on the underlying data source. If the table is very complex, try simplifying the query by reducing calculated columns, measures, or filters and then attempt the export again.
2. The error could be occurring due to a timeout or performance issue on the data source side when exporting large datasets. DirectQuery requires real-time querying from the data source, and if the query is slow or times out, the export operation will fail.
3. Try reducing the number of columns in the visual before attempting to export. Since you mentioned that reducing columns works, this indicates the query might be too large or complex when including many columns. You can also attempt exporting the data in parts. Export smaller chunks of data by selecting subsets of the table or creating separate visuals with fewer columns and exporting them individually.
4. There may be a configuration or permission issue in the workspace or report settings that is causing the export to fail. Ensure that your Power BI Service permissions allow data export. Report consumers need to have the necessary permissions (e.g., Build and Export Data rights). Check the Export data settings under File > Settings > Export options for any restrictions that might be applied to your report.
5. Check the Power BI Service Activity Log or error messages in the Power BI portal for more detailed error information that can help you identify the specific issue causing the failure.
6. If the data export functionality continues to fail, you might consider using Paginated Reports. Paginated Reports are optimized for large-scale printing and exporting of data and can handle larger datasets more efficiently.

If this helps, please mark it ‘Accept as Solution’, so others with similar queries may find it more easily. If not, please share the details.

[nm17844]

@v-hashadapu Thanks for your response!!!

Its just one table with 44 columns and around 120837 rows and there are no calculated columns and measures.

I will check all the options mentioned by you.

[v-hashadapu]

Hi @nm17844 , Please let us know(while providing details) if you have any further queries relating to this issue or if our workarounds and suggestions didn't help. Also, if your issue is solved, please consider marking my reply 'Accept as Solution'. 

[FarhanJeelani · Accepted solution]

Hi @nm17844 ,

The error you're encountering during export in Power BI Service is likely due to limitations with DirectQuery mode or memory constraints. Here are some suggestions to address the issue:

1. Reduce Query Complexity: Simplify the query by reducing the number of calculated columns or measures used in the visual.

2. Aggregate Data: If possible, pre-aggregate data to reduce the volume queried at runtime.

3. Increase Column Export Limit: Adjust the MaxRows or MaxColumns settings in the Power BI Desktop export configuration (if applicable), though this may not fully resolve service-side limitations.

4. Test with Import Mode: Temporarily switch the dataset to Import mode to see if the issue persists.

5. Optimize Filters: Ensure filters applied in the visual reduce the dataset size effectively.

6. Log Support Ticket: If none of these work, consider raising a support ticket with Microsoft for assistance.

Please mark this as solution if it helps. Appreciate Kudos.

[nm17844]

@FarhanJeelani 

Thanks for the response!!!

Since the data size is small I will try this with import mode.