Forum Discussion
Advocate ISharePoint Online and Gateway
Hi febyte ,
You can choose SQL Server database connector with DirectQuery mode or Import mode to connect on premise SQL Server, see :Refresh data from an on-premises SQL Server database, and choose SharePoint folder connector to connect excel files of SharePoint online at the same pbix file in Power BI Desktop, see: Loading Excel Files from Sharepoint.
Since your database combines on premise data source and cloud data source at the same time, the gateway need to be installed and set, add the two data sources of your dataset to gateway in Power BI Service, as described in Add a data source. Then you can use manual refresh or schedule refresh to get updated data, see more :On-premises data gateway , Configure scheduled refresh .
Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.
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.
Hello Gladiator909 , febyte , did you find a solution?
I'm facing the same issue and I don't want to refresh Sharepoint sources through my On Prem gateway (use to refresh Oracle Data).
I can refresh if I use only relationship but not if I use merge or appen table.
Helper IColineH so basically what I did was, for the sharepoint source, I created a "DataFlow" on PowerBI service to grab my excel sheet that was on sharepoint. Then on Power BI Desktop, I bring in that particular dataflow and append it to the table from my Oracle database. The auto refresh should work without any issues since you are refreshing a dataflow and the oracle table. Just remember to refresh your dataflow before you refresh the appended tables. Let me know if you have any questions.
Thanks for your answer, I will test it very soon and I tell you, I never done a dataflow.