SharePoint Online List data does refresh on Power Bi Service

[Anonymous]

Hi, I have created a table that has data from two sources. one is the SharePoint Online list and another one is an On-prem SQL server.

I am getting some trouble with the refresh of that new table in the Power Bi service. I can refresh my merged table on the desktop application but when I publish that dataset to the service and set up enterprise gateway( SharePoint list and Sql server both On-Prem) and refresh I see from that merged table SharePoint data vanishes. This means that the merged table for some reason doesn't show the SharePoint Data.

For testing, I have then downloaded the Dataset locally and I can see my Applied steps are as is and when I refresh it in Power Query data does come back. Not sure what is happening. Any help will be appreciated.

Regards

Roshan

[Anonymous]

Thank you for your reply. I have tried all these settings beforehand but nothing worked. However, I have found the solution and that is the user used to configure the gateway didn't have access to the SharePoint list. hence when I was refreshing the report on desktop it working fine whereas in Service it was failing.

I appreciate your comment though.

[_sfrost · Accepted solution]

@Anonymous 

Make sure you check "Allow user's cloud data sources to refresh through this gateway cluster" under Gateway Cluster Settings. This is required since you are merging cloud and on-premises data.

For more info, please refer to the following doc:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

Did I answer your questions? Give a thumbs up and accept this post as solution!