Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tpaul
New Member

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

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

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.

v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

@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"

venal
Memorable Member
Memorable Member

@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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors