cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tpaul Frequent Visitor
Frequent Visitor

How to refresh data from on-premise SharePoint Excel File

We have certain org SharePoint sites that are still on SP 2013 on-premise.  We have a Power BI report using an Excel file on this on-premise site.  We've connected using the "Web" get data function using the URL of the Excel file and using Windows credentials (it doesn't allow any other authtentication method).  After we create a report and publish the dataset/report, we cannot schedule a refresh on the service.  When I look at the Dataset in the service it shows this:Credentials Error.png

None of the Authentication methods work when I try to edit the credentials.  I've tried all 3 - Anonymous, Windows and Basic and get this error no matter what choose:

Anonymous Error.png

Is there a special method for scheduling a refresh from an on-premise SharePoint file?  Do I need to have our Admin add SP to the on-premise Gateway?  Refreshing the data works in the desktop app, but we have to keep republshing the report to reflect the updated data.

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
tpaul Frequent Visitor
Frequent Visitor

Re: How to refresh data from on-premise SharePoint Excel File

Thanks.   Unfortunately, the gateway is under control of our admins so I can't add SP 2013 to it or modify any settings and we're not going to add SP 2013 to the org gateway.  We'll have to move the file to One Drive or wait until the site is migrated to the cloud in order to schedule a refresh of the data to the reports.

View solution in original post

4 REPLIES 4
venal Senior Member
Senior Member

Re: How to refresh data from on-premise SharePoint Excel File

@tpaul 

Click "Gateway Connection" and add your data source under the gateway.

After that click on the "Data Source Connection" and enter your credentials(try to maintain same credentials and privacy levels in Power BI Desktop, Service, Gateway).

 

If the issue is persists, please let us know.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

Ross73312 Super Contributor
Super Contributor

Re: How to refresh data from on-premise SharePoint Excel File

@tpaul 

 

I've done this before myself so imagine your Power Query code will be a Excel connector inside a Web Connector.  Your gateway will only need the Web connector, with the User/Pass required to get to that excel file.

 

It should be remember that your on-premise data gateway will be making the connection, so you need to make sure it has access to the SharePoint site. You may need to make sure the Username you use contains the domain such as "CORP\Service.Account"


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Community Support Team
Community Support Team

Re: How to refresh data from on-premise SharePoint Excel File

HI @tpaul ,

I'd like to suggest you use to add your credentials in your connector as optional parameters and split your URL to root path and relative path.

let
    Source = Web.Contents("https://api.xxxxx.com", [Headers=[Authorization=xxxxxxxxx],RelativePath="/vx/xxxxxxx"])
in
    Source 

In addition, if your on-premise SharePoint server is stored internal network, you need to set proxy on the gateway side.

Configure proxy settings for the on-premises data gateway 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
tpaul Frequent Visitor
Frequent Visitor

Re: How to refresh data from on-premise SharePoint Excel File

Thanks.   Unfortunately, the gateway is under control of our admins so I can't add SP 2013 to it or modify any settings and we're not going to add SP 2013 to the org gateway.  We'll have to move the file to One Drive or wait until the site is migrated to the cloud in order to schedule a refresh of the data to the reports.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,189)