Excel - File Contains Corrupt Data

[aaron_wang]

I currently have a report which runs off of scheduled excel extracts from our ERP. It was working fine until we had a system update and now I get the below error message when trying to refresh from those files.

I have checked that there is no password, change in the file properties, run "Check for Issues" in excel, rebuilt the report with new connections to the files with no luck. If I open the file and then hit save without changing anything, the PBI report will refresh without any issues so I don't think its to do with any data in the file. I can also refresh the dataset without any issues in PBI desktop so I am not sure why PBI services is giving the error

Any help would be greatly appreciated, thank you!

[123abc]

The error message you're encountering, "File Contains Corrupt Data," can be frustrating, but there are several steps you can take to troubleshoot and resolve this issue. It's good to hear that the data refresh works in Power BI Desktop, which suggests that the issue might be related to the Power BI service or how it's interacting with the file. Here are some steps you can try:

1. File Compatibility and Version: Ensure that the Excel file format you're using is compatible with Power BI Service. Generally, Power BI Service supports .xlsx files created in recent versions of Excel. If your file is in an older format, try converting it to the latest format.

2. Data Source Settings: Double-check the data source settings in your Power BI report to make sure they are correctly configured to connect to the Excel files. Sometimes, a small change in the file path or connection settings can lead to errors.

3. File Location and Permissions: Ensure that the Excel files are stored in a location accessible to Power BI Service, and that the service has the necessary permissions to access these files. Permissions issues can lead to corruption errors.

4. Scheduled Refresh Configuration: Verify the settings for scheduled refresh in Power BI Service. Ensure that the refresh frequency and credentials (if needed) are set correctly. Sometimes, refreshing too frequently or with incorrect credentials can lead to errors.

5. Data Load and Transformation: Review the data loading and transformation steps in Power Query within Power BI Desktop. Ensure that there are no custom transformations that might be causing issues. Consider simplifying or removing any complex data transformations temporarily to see if that resolves the issue.

6. Data Validation: Double-check your data sources for any potential data issues that may not be apparent in Power BI Desktop but could be causing problems in the service. Ensure that there are no hidden or corrupted worksheets or cells in your Excel files.

7. Service Updates: Keep an eye on updates and announcements from Power BI Service. Sometimes, issues like this can be related to service updates, and Microsoft might release fixes or workarounds.

8. Power BI Support: If the issue persists, consider reaching out to Power BI support. They can provide more specific assistance and might be able to diagnose the problem further.

9. Alternative Data Sources: If all else fails, you might consider using an alternative data source or format (e.g., CSV, SQL database) for your report if feasible.

10. Manual Refresh: As you mentioned, manually opening and saving the Excel files seems to resolve the issue temporarily. You could automate this process using a script or a scheduled task as a workaround until the root cause is identified and fixed.

Remember to back up your files and reports before making any major changes or troubleshooting steps. Corrupt data issues can sometimes be elusive, but thorough testing and careful examination of each component can often help identify the root cause.