none
Powerquery unable to import data from another excel stored in sharepoint online

    Question

  • Hi,

    Here is the scenario:

    I have a source excel 'Excel1' stored in Sharepoint online library. In the same site I have another excel workbook ('Excel2') wherein I want to pull complete worksheet data from Excel1 using PowerQuery. Subsequqntly I want to use this data in 'Excel2' for Powerviews.

    Problem is that when I import data into excel2 using import fromxls file, I get the error "DataFormat.Error: External table is not in the expected format."

    I was able to do the same few days back and now its not working. Appreciate quick help or any pointers.

    Thanks,

    Pushkar

    Saturday, January 04, 2014 1:41 AM

Answers

  • The error indicates that the data returned by SharePoint was not an excel file, but an html page for the user to login. Could you make sure you are connected to the SarePoint site using the correct credentials and able to resolve that Excel file?

    Thanks


    Zafar Abbas

    Tuesday, January 07, 2014 2:23 AM

All replies

  • I'm having a similar problem--I'm working on a local file "Excel1." I have an "Excel2" file hosted on my Sharepoint document library. From Excel 1, I try to use Power Query to Import a File>Excel>Sharepoint URL. I can see the document library, and can select Excel2, but I get the same error when the query loads. I've tried other Excel files on Sharepoint and always get that error. But I can import fine from other local Excel files. 

    I'm also using the Power BI demo, with Sharepoint 2013 and the Power Query preview. 
    Sunday, January 05, 2014 3:29 AM
  • The error indicates that the data returned by SharePoint was not an excel file, but an html page for the user to login. Could you make sure you are connected to the SarePoint site using the correct credentials and able to resolve that Excel file?

    Thanks


    Zafar Abbas

    Tuesday, January 07, 2014 2:23 AM