Forum Discussion
DataFormat.Error: We were unable to load this Excel file because we couldn't understand its format.
Hi HanafiHalim ,
Thank you for your response.
Power BI’s connector for Excel files stored in SharePoint sometimes fails to interpret the file format correctly sometimes if the file was generated by a third-party tool like RemedyForce or if the file contains hidden metadata or encoding quirks. And sometimes due to Power BI Service received an update that changed how it parses Excel files.Instead of manually opening each file use a Power Automate flow to open and re-save the Excel files after upload.Open Power Automate Desktop
Create a new flow:
Use For Each File in Folder action pointing to your SharePoint synced folder
Use Launch Excel, Open Workbook, and Save Workbook actions
Close workbook
Save and run the flowYou can even set this flow to trigger after a file upload or on a schedule.
Hi HanafiHalim ,
Thank you for your response.
Power BI’s connector for Excel files stored in SharePoint sometimes fails to interpret the file format correctly sometimes if the file was generated by a third-party tool like RemedyForce or if the file contains hidden metadata or encoding quirks. And sometimes due to Power BI Service received an update that changed how it parses Excel files.
Instead of manually opening each file use a Power Automate flow to open and re-save the Excel files after upload.Open Power Automate Desktop
Create a new flow:
Use For Each File in Folder action pointing to your SharePoint synced folder
Use Launch Excel, Open Workbook, and Save Workbook actions
Close workbook
Save and run the flow
You can even set this flow to trigger after a file upload or on a schedule.
"Use Launch Excel, Open Workbook, and Save Workbook actions" - These don't exist in Excel Online Business.
Hi pb1051 ,
Since Excel Online Business doesn't support direct file opening and saving actions, you can instead automate the file processing using Excel Online Business connectors. You can set up a flow to get the files from SharePoint. Recreate the data processing in Power Automate (e.g., clearing any issues that are causing Power BI to fail when reading the file) and save the file again in SharePoint.
Hope this helps.
Thank you
