Forum Discussion
Helper IIData On-Premises Vs. SharePoint Online
Tara_ I would recommend you to keep the excel files in SharePoint online. So that we can avoid the dependency with Gateway.
You have to use the SharePoint Folder connector for getting the excel files into Power BI.
You can connect SharePoint online directly with Power BI desktop. If needed, you can use dataflow as well.
Also, you can schedule the daily/weekly data refresh. or you can use Microsoft Power Automate to trigger the data refresh after a new file added to the SharePoint.https://powerbi.microsoft.com/en-us/blog/refresh-your-power-bi-dataset-using-microsoft-flow/
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Community SupportHi Tara_ ,
Recommend you save your data in SharePoint Online. Then you can use SharePoint Folder connector to get data in Power BI Desktop. See detail steps: Connecting to Files in SharePoint & OneDrive with Power BI / Power Query. In this way, it doesn't need to install and set gateway if your dataset only contains the cloud data source. Also, there are some advantages using SharePoint Online connector.
1.When you sign in with your Microsoft account, select Keep me signed in. Power BI can then synchronize any updates you make in the file in Power BI Desktop with datasets in Power BI.
Any reports you' ve created in the file are loaded into Reports in the Power BI service. Let's say you make changes to your file on OneDrive or SharePoint Online. These changes can include adding new measures, changing column names, or editing visualizations. Once you save the file, Power BI service syncs with those changes too, usually within about an hour.
2.You can connect to multiple files instead of just one when connecting to your files this way.
3.You reduce the risk of ever hitting a Formula. Firewall since all of the files inside that folder will be set with the same Privacy Level and will fall under the same scope
4.The SharePoint connector was created in a way where it uses the SharePoint API to grab the data. The connector also allows, to a certain degree, Query Folding so when you run your query you’re only getting the files that you’re actually interested in and not all of the files from your SharePoint site. You can read more about Query Folding here.
In addition, you may configure Schedule refresh for the SharePoint file, see Configure scheduled refresh.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IIThank you v-xicai for the response and links. I connected to the Sharepoint Folder successfully, however I have a couple of more concerns:
1. After connecting to the SharePoint folder, in power query I was unable to seperate both files after clicking combine. It only created one table for one of the files. Instead what I did was I copied the query to create two tables and it worked but I don't think this is the correct way plus I don't know how it would effect scheduled refresh on PBI Service. Could you confirm?
2. Assume the two files that will be updated bi-weekly are Sales and Products. Should I put all Sales files in one folder while all the Product files in another, in SharePoint online, and then use two SharePoint folder connectors to connect to Power BI? What would be the best practice for this scenario?
Thank you