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
Hi,
you're right: Sharepoint online for O365! and yes, "automatic refresh" is what I expect.
To clarify the "gateway" topic:
- I didn't deploy any, as I'm expecting the refresh to work "as is"
- nevertheless, when requesting a manual refresh of the dataset in Power BI services (my report doesn't update automatically), I receive an error message as if I'd have to configure a gateway (see attach.)
Thanks for helping
That isn't SHarepoint Online for Office 365. That file path is a local file path to an on-prem sharepoint server. You have to install and configure a gateway.
edhans Anonymous
Thank you guys! I guess I learnt what "on-premise" files means :-). My company indeed uses its own servers for our cloud based Office365 deployment.
Based on your feedback, I understand that I don't have any other choices: I need to deploy a gateway.
Thanks again to nail it down quickly.
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
I've been confused on this for quite some time searching for the difference between SharePoint Online and SharePoint On-premises and why I'm receiving a "Dataset requires a proper configured Gateway..." error like above. This thread is the closest to any information I can find out on the difference but still don't have all the information!! I wish there was an article to provide the SharePoint difference AND how to resolve the Gateway issue. (Troubleshooting Gateways from docs.microsoft.com didn't help).
Via Desktop, when you 'Get Data' and select SharePoint Folder - then access the datasets (all separate excel files stored in a SP Folder), once you publish to the Service, you receive the Gateway error message. It sounds like you need a Gateway IF you Get data this way. However, if I use via Desktop 'Get Data" and select 'Online Service' and Select 'SharePoint Online List'. That's the only thing you can use - a list on Sharepoint, it will auto-refresh the data in the service. What I need is to use indiviual excel files stored within a Document Library folder. It's almost like there isn't an option to use individual excel files stored on a SharePoint Document Library via Online so that I won't need a Gateway? If you know how to do this, please drop in the instructions. I've read so many articles & threads but nothing seems to pinpoint a solution on this issue.
1. This doesn't work without a Gateway? https://powerbi.tips/2016/09/loading-excel-files-from-sharepoint/
2. You don't need a Gateway but can't access individual excel files in a SP Doc Libarary unless you use OData? (have yet to try this) https://community.powerbi.com/t5/Integrations-with-Files-and/Fetch-Live-data-from-Sharepoint-Online-List-and-publish-it-as-a/td-p/111679
3. Another idea with Sharepoint; Use Sharepoint - Teams and it will work like OneDrive but in SharePoint?? https://docs.microsoft.com/en-us/power-bi/service-comma-separated-value-files
Bottom line, I have excel & csv files stored in a SharePoint Document Library. Those files are updated weekly via a Microsoft Flow that overlays the files each week. The PBI reports were created via Desktop and Published out to Power Bi Service. I need Power Bi Service to REFRESH the reports (Dataset) automatically WITHOUT me having to go into Destop, hit Refresh and then Publish again to the Service overlaying my existing reports.
