Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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!
Solved! Go to Solution.
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.
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.
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
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"
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?