cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olimilo Member
Member

"External table is not in expected format" when connecting to Sharepoint file

I keep getting the error when I try to get data from a XLSX file (made and saved with Excel 2016) in Power BI. Which is weird, because I tried getting the data through Excel's Get Data > Web and the file and its data loaded just fine. So I'm just getting the error in Power BI. Is there any way I can fix this? I've already installed the 64-bit version of the 2010 Access Database Engine. Would I need to install the 2016 version as well to be able to connect to my file online?

 

 

Thanks Smiley Happy

 

9 REPLIES 9
Moderator v-yuezhe-msft
Moderator

Re: "External table is not in expected format" when connecting to Sharepoint file

@olimilo,

What is the bit version of Power BI Desktop do you use? Do you also use the Web entry in Power BI Desktop? If so, please use SharePoint connector instead to connect to the Excel file and check if you get any errors.

In addition, please directly open the Excel file, check if you get restricted Access message, and check if the sheets are locked.  

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
olimilo Member
Member

Re: "External table is not in expected format" when connecting to Sharepoint file

Hi @v-yuezhe-msft Lydia, I'm using the Feb 2018 update. I guess I was able to connect to a single file (using the Get Source -> Web option and using the direct link to the file) but when I'm trying to connect to a Sharepoint folder, I'm getting this error:

 

2018-02-21 15_50_14-.png

 

Also, I can't seem to access the Organizational Account in this dialog box:

 

2018-02-21 15_52_35-.png

 

Using my current credentials and I get an Access is forbidden error.

Moderator v-yuezhe-msft
Moderator

Re: "External table is not in expected format" when connecting to Sharepoint file

@olimilo,

Do you connect to Excel file located at SharePoint Online? If so, use Microsoft account authentication type instead.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
olimilo Member
Member

Re: "External table is not in expected format" when connecting to Sharepoint file

Hi again @v-yuezhe-msft Lydia,

 

I was following the instructions on this page.But as I was saying, I was getting this error: 

 

 

 

2018-02-21 15_50_14-.png

 

Do you know how to resolve this? Would I be needing additional permissions for this? I'd like to expound on this so that I can file a proper report to our tech support to get assistance in resolving this issue.

 

Thanks Smiley Happy

Moderator v-yuezhe-msft
Moderator

Re: "External table is not in expected format" when connecting to Sharepoint file

@olimilo,

I guess you enter wrong SharePoint site URL in Power BI Desktop. Could you please use web entry in Power BI Desktop to connect to the Excel and check if it is successful?

1.JPG2.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
olimilo Member
Member

Re: "External table is not in expected format" when connecting to Sharepoint file

Hi @v-yuezhe-msft

 

I'm not trying to connect to just one file, I'm trying to connect to a Sharepoint folder so that I can combine the files later on. This is the DAX I'm using to connect:

 

= SharePoint.Files("https://XXX.sharepoint.com/XXX/XXX/Data%20Repository/2017%20Data/", [ApiVersion = 15])

And getting this error when running that:

 

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://XXX.sharepoint.com/XXX/XXX/Data%20Repository/2017%20Data
    SPRequestGuid=c08c4e9e-40ad-5000-2e06-4a5e113c0872, c08c4e9e-b0c4-5000-2e06-46e20323d4c8, c08c4e9e-f0e1-5000-2e06-4b09f931104f

I can connect to a single file from Sharepoint without hassle though. But we can't just dump all of our data onto one file to use with Power BI given the size of each part.

Moderator v-yuezhe-msft
Moderator

Re: "External table is not in expected format" when connecting to Sharepoint file

@olimilo,

Please enter root site URL. Do you add folder name in the following URL?

https://XXX.sharepoint.com/XXX/XXX/Data%20Repository/2017%20Data/

Regards,

Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
olimilo Member
Member

Re: "External table is not in expected format" when connecting to Sharepoint file

Hi @v-yuezhe-msft

 

I give up, I can't get this to work properly with Sharepoint so I just got 1:1 copies of the Sharepoint files onto my local drive. However, I'm still getting the same "External table is not in expected format" error when I load the Excel (2016) files from a local folder. I'm assuming there's no size limit when it comes to loading files because the files I have is clocking at 120 MB. Also, there wouldn't be any need for credentials right? The files are stored locally in my Documents folder so I have full read/write access to it. Is there any way fix to this?

Moderator v-yuezhe-msft
Moderator

Re: "External table is not in expected format" when connecting to Sharepoint file

@olimilo


Could you please share the Excel file via Private Message ? I will test it in my environment.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.