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 ,
We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Best Regards,
Chaithra E.
Hi,
Not sure to say it is fixed, but yeah I am able to refresh PowerBI now.
for now what i do is:
1. Open excel file after upload it.
2. Close it without do anything.
3. Refresh PowerBI.
I have around 4-7 files need to upload to sharepoint. To open all files is time consuming because of how large data in it.