Forum Discussion
Automatic dataset refresh from Excel data source on Sharepoint
nhenri ,
If you are using on-premises SharePoint, gateway is required. If your are using SharePoint online, gateway is not required, just change data source of your Power BI report in Power BI Desktop.
Regards,Lydia
AWentzEco - SHarepoint Online and Sharepoint on Prem are the same basic product, at least from the end user perspective. If publishing Power BI reports though you have to know which you have.
When writing a report in the Desktop, if you are at work, it will connect with no issues to both On Prem and Online, because you are there. But once published, the Service has no way to get to your on prem server without a gateway. It can connect to Sharepoint Online with no gateway, you just need to add your credentials.
If you are getting a "needs gateway error" you must have on prem for your data source, and you need a gateway so the service can reach it. Think of the gateway as a VPN that allows the service to get to on prem resources. Can be anything almost. SQL Server, Sharepoint on Prem, network drives, Access databases, etc.
edhans Thanks for the clarification. This definitely helps. But I'm still confused on how to access SharePoint Online when using 'Get Data' via Desktop. It's like the only option is a SharePoint List - but there must be a way to use SharePoint Online, then use/link to individual files stored in a Doc Library. I cannot find any instructions on how to do this....but there must be a way. That way, I would be linked to SharePoint Online and can use the Refresh in Service.
***Updated****
Ok, I stand to be corrected. I created the Data Source links quite a while ago. I thought I originally went in and linked via SharePoint folder, then changed "SharePoint.Files" to "SharePoint.Contents" - found the Folder, grabbed each file individually.
Looking back at my Queries for each table, it appears I connected via 'Text/Csv' since all I have is:
Source = Csv.Document(Files.Contents("C:\Myinfo....") for each one.
With connecting via SharePoint the steps should have been:
Source = SharePoint.Contents("https://....)
Navigation = #"Your Document Library Name{[Name=".."]}[Content]
Imported CSV = Csv.Document....
Which I believe will actually allow for a Refresh on the Service without a Gateway. (That was a lot of research effort for such an idiot mistake on my part.) Rule #1, always look back at your Source Step...