SharePoint On Premise Connection to Excel File

[wmorris20]

We have an enterprise data gateway succesfully setup, but I'm unable to figure out the connection string necessary to connect to an Excel file which is on premise and inside a subsite.

http://[site]/[subsite]/Shared%20Documents/[file.xlsx]

I haven't been able to find documentation that describes that string necessary (or even if this is possible).  I have been able to connect to a SharePoint folder at the root site, but I need a specific file within a subsite library.

Thanks for any help you can offer.

[v-yuezhe-msft · Accepted solution]

Hi @wmorris20,

Based on my test, you can use one of the following methods to connect to Excel on SharePoint subsite in Power BI Desktop.

1. Copy URL of Excel file as shown in the following screenshot, then paste it to “Get Data>Web” entry in Power BI Desktop, please note that you would need to exclude “?Web=1” from URL of Excel file.

Then choose Windows method in the following screenshot, enter your account and password, you will be able to load data from your excel file.

2. Paste URL of your SharePoint subsite in the following screenshot, choose Windows method in the next screenshot, enter your account and password.

After loading data in Power BI Desktop, in the query editor window, there will be Content column, along with an icon that appears to be two down arrows. Click on the icon and you'll be able to select table or sheet from your excel file.

Thanks,
Lydia Zhang

[wmorris20]

Thank you for the very detailed response and solution.