Forum Discussion

Virtual_Ames's avatar
Virtual_Ames
Microsoft Employee
10 years ago

[DataFormat.Error] External table is not in the expected format.

Community,   I am having a problem importing files from a sharepoint folder (6 files that need to be brought together). I see these as .xls files in Sharepoint and therefore use:        let     ...
Need Help
Andrea's avatar
Andrea
Microsoft Employee
9 years ago

I have the same error, does somebody find a workaround?

przemekkucia's avatar
przemekkucia
Advocate II
9 years ago

Same error,

 

In our case it seems that access restrictions create the problem. Even with full control given to me. After removing them it worked like magic. 

 

Sadly, if files have to be restricted - we haven't found any workaround. (Power Query for Excel also didn't work)

  • lme062's avatar
    lme062
    New Member
    8 years ago

    I'm trying to access a JetReport .xlsx shared to me from a Sharepoint site

    It also fails with "External table is not in the expected format"

     

    Any one had experience with using shared excel files in PowerBI?

    • Johanno's avatar
      Johanno
      Continued Contributor
      8 years ago

      I saw in another forum that someone wrote that this can occur when you export to Excel files from third party software. A workaround that worked for me is that if you open each file with Excel and save it, after this you can import it. It's not a good solution though if you have many files.

      • grechill's avatar
        grechill
        Helper I
        6 years ago

        Hi Johanno ,

         

        Unfortunately it didn't work for me. Tried re-saving as xlsx, no success. Tried copying to new Excel file as data and saving as xlsx, no success. 

         

        BUT

        No wonder, saving as CSV works as there is no XML involved.

  • Krishna09's avatar
    Krishna09
    Helper III
    3 years ago

    hi ,

    how to remove access restrictions?

    I am facing same error. In my case i have power automate flow. this flow creates .xls file everey day in sharepoint. 

    and trying to connect to power query with schduled refresh.

    but when I open xls file in browser and try to change from read to edit, it asks if it can be converted.

    so i think if I can save file editable then there will be no issues.

    so can any one guide me either how to remove access restrictions or save editable file in sharepoint with power automate flow?