Connecting to Excel in SharePoint On Prem

[Anonymous]

Hi all, 

I'm having an issue with Power Queries that I used to import some excel files from SharePoint 2013 on Prem to my Power BI Desktop environment. 

The Power Query itself is nothing but importing excel files from ShaprePoint using built-in Excel connector and some basic operations such as applying filters. It always works fine when the query is written, but every once a while (normally in a couple of days), it throws back errors in Query Editor when I'm about to refresh the report. 

The same file path error returned for all the Power Queries importing Excel files using the same method, but the file path, in fact, works fine. Interestingly, when I clicked the "Edit Settings" botton and pointed the Excel connect to the same path again, it works perfectly..for a couple of days. 

It'd be much appreciated if this can be resolve and the repeated process doesn't need to be done periodically.  

Although I have a feeling this may be more of a SharePoint issue, I still decided to try my luck here. Thanks in advance. 

Some basic info: 

Power BI Pro,

SharePoint 2013 on Prem

[Anonymous · Accepted solution]

Just an update that this issue is solved by changing the power query and file path. It's similar to what @Anonymous suggested here, plus that the power query also needs to be changed from Excel.Workbook(File.Contents(.. to Excel.Workbook(Web.Contents(..

When importing excel file from SharePoint 2013 on Prem using Excel connector and select the file using the prompt navigation window, the power query will be formularised as below by default: 

Excel.Workbook(File.Contents("\\DomainName\...\...\Folder Name\File Name.xlsx")...

This will trigger the DataSource.Error in the next couple of days.

To solve this issue, you can either copy, paste and refresh EVERY power query imports data in the same way to make it work or modify the power query and path as below: 

Excel.Workbook(Web.Contents("https://DomainName/.../.../Folder%20Name/File%20Name.xlsx")...

Hopefully this can help people with the same issue. 

@v-yingjl Connecting excel files from SharePoint using excel connector and select the path using the prompt window seems to be nature and reasonable, but errors will be triggered in the next couple of days if power query and path are not manually modified. I'm not sure if it's a SharePoint 2013 on prem only issue, but perhaps it's still worthy for Power BI/Microsoft team to investigate if it's something needs to be fixed or something worth mentioning in the relevant documentation.

Cheers 

[v-yingjl]

Hi @Anonymous ,

Thanks for your share and suggestions!

You can submit the requirement to https://community.powerbi.com/t5/Issues/idb-p/Issues to make it available more quickly.

Best Regards,

Yingjie Li

[v-yingjl]

Hi @Anonymous ,

When you connect it sometimes is ok or sometimes has problems, you can check your network status and try to connect it again.

Or you can download the latest version of power bi desktop and try it again:

https://www.microsoft.com/en-us/download/details.aspx?id=45331

You can refer the following cases that can help you:

1. https://community.powerbi.com/t5/Desktop/Network-path-of-excels-not-found/td-p/113885
2. https://community.powerbi.com/t5/Desktop/DataSource-Error-Could-not-find-file/td-p/178314

Best Regards,

Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[aTChris]

@Anonymous 

As you suggest I don't think this issue is Power BI related but as the error states Network Path.

One thought I've had is that the network connection is reset and the action of you connecting establishes a new connection. Do you have the location mapped in Explorer as a drive or do you connect from PBI each time? You might find having that path defined in your computer maintains the connection therefore its always available to power bi.

Hope you find the answer...