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

Connecting to excels on SharePoint

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

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
hugoberry Member
Member

Re: Connecting to excels on SharePoint

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)

View solution in original post

6 REPLIES 6
hugoberry Member
Member

Re: Connecting to excels on SharePoint

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)

View solution in original post

JaredK Member
Member

Re: Connecting to excels on SharePoint

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?

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
Community Support Team
Community Support Team

Re: Connecting to excels on SharePoint

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Matthias93 Member
Member

Re: Connecting to excels on SharePoint

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 Member
Member

Re: Connecting to excels on SharePoint

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

Matthias93 Member
Member

Re: Connecting to excels on SharePoint

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors