Enterprise Data Gateway not showing up when an Excel file from SharePoint site (Online) is used?

[BBIUser]

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!

[GilbertQ · Accepted solution]

Hi there

You need to add the Excel file with the same data source details to your Gateway in order for it to work.

[v-piga-msft · Accepted 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

[v-piga-msft · Accepted 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

[BBIUser · Accepted solution]

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! 

[v-piga-msft]

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

[GilbertQ]

Hi there

What that setting allows you to do, is to be able to have the Gateway refresh both cloud and on-premise data sources together. Which will allow for them to work together as they do in Power BI Desktop

[BBIUser]

@GilbertQ Thanks for the insights!

[GilbertQ]

Awesome, glad you got it all working

[GilbertQ · Accepted solution]

Hi there

You need to add the Excel file with the same data source details to your Gateway in order for it to work.