Connecting to excels on SharePoint

[Matthias93]

Hi,

I'm facing a strange issue in my dashboard. I'm getting data from multiple excels that are being stored on a SharePoint site. For some reason everytime I try to refresh I get the issue as you can see in the picture. The only way I have found to resolve this so far is to each time go to the query editor and reselect the excel and excel tab in the source and navigation step of the query. The path in the advanced editor is exactly the same before and after this operation. From a user perspective this really isn't a good thing. I just find the whole issue very strange. Has anybody encountered this before?

Kind regards,

Matt

[v-shex-msft]

Hi @Matthias93

As hugoberry said, if you can please use sharepoint connector to connect the datasource. It may work through the issue.

Regards,

Xiaoxin Sheng

[Matthias93]

Hi,

Is the only way to achieve this to create entirely new queries and relationships, or can I alter the existing query?

Thanks for your help.

Regards,

Matt

[hugoberry]

You can amend the query with replacing the data connection step with the example above. This should not require recreating the relationships.

[Matthias93]

Hey guys,

I slightly changed your path and it works perfectly. So far I get no errors when refreshing. Thanks a lot for helping me out.

Kind regards,

Matt

[Anonymous]

Hi @Matthias93, how did you originally connect to the SharePoint location? Did you use GetData > SharePoint option, or did you use the Web connection option?

[hugoberry · Accepted solution]

I found that SharePoint webdav endpoint quite unreliable. I would recommend using the REST interface instead. Especially because you can access SharePoint lists directly from Get Data menu.

Your query might eventually look like this

=Excel.Workbook(Web.Contents("https://irm.be.deloite.com/Dahsboards excels/Retention.xlsx"),null,true)