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

[olimilo]

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 🙂

[v-yuezhe-msft]

@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

[olimilo]

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:

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

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

[v-yuezhe-msft]

@olimilo,

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

Regards,
Lydia

[olimilo]

Hi again @v-yuezhe-msft Lydia,

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

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 🙂

[v-yuezhe-msft]

@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?



Regards,
Lydia

[olimilo]

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.

[v-yuezhe-msft]

@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

[olimilo]

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?

[v-yuezhe-msft]

@olimilo，

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

Regards,
Lydia