Forum Discussion

HanafiHalim's avatar
HanafiHalim
New Member
1 year ago
Solved

DataFormat.Error: We were unable to load this Excel file because we couldn't understand its format.

When trying to refresh a report manually by clicking refresh now button on Power BI Services after upload files on sharepoint, this error popup "DataFormat.Error: We were unable to load this Excel fi...
Need Help
  • v-echaithra's avatar
    v-echaithra
    1 year ago

    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.

jaineshp's avatar
jaineshp
Icon for Memorable Member rankMemorable Member
1 year ago

Hey HanafiHalim,

Quick fixes to try:

  • Save your Excel file as .xlsx format and re-upload to SharePoint
  • Clear browser cache, then hit refresh again
  • Check if the file opens fine in Excel desktop first

If still failing:

  • Try uploading the file to a different SharePoint folder
  • Create a new dataset connection to the same file
  • Remove any charts/pivot tables from the Excel file temporarily

Root cause: Since this worked before June 16th, likely a recent Power BI service update is causing the issue. Your ticket #2508180030006077 should help Microsoft track this.

 

Bottom line: Try the file format change first - that fixes most of these errors. If not, it's probably on Microsoft's end to resolve.

 

Fixed? ✓ Mark it • Share it • Help others!


Best Regards,
Jainesh Poojara | Power BI Developer

HanafiHalim's avatar
HanafiHalim
New Member
1 year ago

Quick fixes to try:

  • Save your Excel file as .xlsx format and re-upload to SharePoint
    • File format is .xlsx
  • Clear browser cache, then hit refresh again
    • Tried clear cache, I even accidently clear all my browser history. Not working as well.
  • Check if the file opens fine in Excel desktop first
    • Yes, file opens fine in excel desktop and sharepoint. And seem, currently i have to open all file first before refresh on powerbi. No format error popup after it.
    • FYI : file is extract from RemedyForce, previously i directly upload to sharepoint then refresh powerbi, but now i need to open file first before refresh powerbi.

If still failing:

  • Try uploading the file to a different SharePoint folder
    • I have multiple report with different SharePoint folder, all got same error.
  • Create a new dataset connection to the same file
    • Not try yet, I am trying to do dimension setting as provided by microsoft support engineer
      • Update your M query to ignore the incorrect dimensions.
  • Remove any charts/pivot tables from the Excel file temporarily
    • No charts/pivot, just data.