Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I use an Excel file which is in the SharePoint Online (Site) as a source to one of my report on top of SQL Server connection.
This is how I connect to Excel from SharePoint:
1) Get Data > File > Excel
2) Select the Excel file from the SharePoint (File Explorer).
3) I see the Excel Dataset and everything works fine in Power BI Desktop along with other SQL Datasets in that report.
When I publish the report to Power BI Service. I DO NOT SEE the Enterprise Data Gateway (which actually works fine when I remove this excel Dataset from the report).
My questions here are:
a] Am I connecting the right way to SharePoint Online (Excel file)?
b] Do a SharePoint Online need Enterprise Data Gateway to connect and schedule refresh?
(in this case, since we have Gateway setup already for SQL Server, we might need another Data source to be created)
c] Is there any solution?
I did refer to old posts, but I couldn't find a proper solution and also we are not suppose to use Personal Gateway too.
I thought anything connected to Online from Power BI Service does not need Enterprise Data Gateway.
Any help? Thanks!
Solved! Go to Solution.
Hi @BBIUser,
My questions here are:
a] Am I connecting the right way to SharePoint Online (Excel file)?
b] Do a SharePoint Online need Enterprise Data Gateway to connect and schedule refresh?
(in this case, since we have Gateway setup already for SQL Server, we might need another Data source to be created)
c] Is there any solution?
It seems that your report has combine data source incluing on-premise data source and online data source.
Normally, if your data source is online, we don't need to add the data source to data gateway.
For your scenario, you could add both the data source to data gateway and you'd better click the option below for your data gateway.
Best Regards,
Cherry
Thanks for the reply @GilbertQ and @v-piga-msft.
I changed the way I make connection to the Excel in SharePoint. I used 'Web' from Get Data instead of using 'Excel'.
Connecting 'Web' works fine and I do not need the Excel (SharePoint Online) to be added in the Gateway or do not need to apply the option 'Allow user's cloud data sources to refresh through this gateway cluster' in the Gateway Cluster Settings.
But I need to set my data source credentials in the Power BI Service for the web connection I made.
@v-piga-msft I was confused with the highlited text from the screenshot you provided on the Gateway Cluster Settings.
Allow user's cloud data sources to refresh through this gateway cluster - Makes sense to me.
These cloud data sources do not need to be configured under this gateway cluster - ?
When it says 'Allow' in the first statement why does it say 'do not need to be configured'??
Does it mean it is allowed but not required? Thanks!
Hi @BBIUser,
My questions here are:
a] Am I connecting the right way to SharePoint Online (Excel file)?
b] Do a SharePoint Online need Enterprise Data Gateway to connect and schedule refresh?
(in this case, since we have Gateway setup already for SQL Server, we might need another Data source to be created)
c] Is there any solution?
It seems that your report has combine data source incluing on-premise data source and online data source.
Normally, if your data source is online, we don't need to add the data source to data gateway.
For your scenario, you could add both the data source to data gateway and you'd better click the option below for your data gateway.
Best Regards,
Cherry
Thanks for the reply @GilbertQ and @v-piga-msft.
I changed the way I make connection to the Excel in SharePoint. I used 'Web' from Get Data instead of using 'Excel'.
Connecting 'Web' works fine and I do not need the Excel (SharePoint Online) to be added in the Gateway or do not need to apply the option 'Allow user's cloud data sources to refresh through this gateway cluster' in the Gateway Cluster Settings.
But I need to set my data source credentials in the Power BI Service for the web connection I made.
@v-piga-msft I was confused with the highlited text from the screenshot you provided on the Gateway Cluster Settings.
Allow user's cloud data sources to refresh through this gateway cluster - Makes sense to me.
These cloud data sources do not need to be configured under this gateway cluster - ?
When it says 'Allow' in the first statement why does it say 'do not need to be configured'??
Does it mean it is allowed but not required? Thanks!
Hi @BBIUser,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.