Merging tables from two different data sources

[Anonymous]

Gateway settings for Unmerged tablesGateway settings that appear when tables are merged

Hello

I am experiencing a very interesting problem which I'm struggling to resolve and hope you can help.

I have 2 tables from 2 different data source. First table is located on an on-prem SQL server and the second is a spreadsheet located on our companies Sharepoint site.  In Power BI desktop I have merged these 2 tables together and it works fine, data is correctly merged.  The issue lies when I publish to Power Service.  If I publish the tables unmerged I can configure scheduled refreshes through the enterprise gateway for the on-prem data source and the credential settings  appear for the Sharepoint data source.  However when I merge the tables and then attempt to schedule a refresh I am asked to install a personal gateway.  I have tried ignoring privacy settings on data sources but still having the same problem.  I have posted the gateway settings for both scenarios above. Hope you can help

Thanks

Jo

[edhans · Accepted solution]

Is your enterprise gateway set to refresh cloud data? Also, make sure your on prem SQL table is navigating directly to the table. Don't have a source query that is a list of all SQL tables, then your table is actually a reference to the SQL table list, then a navigation to that specific table.

You don't need a personal gateway. Ignore that warning. I don't know why that is in the refresh setting. It is irritating and makes you think you have a need for a personal gateway even when an enterprise gateway is properly set up.

[primolee]

Hello @edhans 

I have encountered similar error when using merge function in Power BI Service.  However, all my data sources are in Sharepoint, so I guess gateways are not necessary as the screen says that "You don't need a gateway for this dataset because all of its data sources are in the cloud, but you can use a gateway for enhanced control over how you connect."

Here is the thread I created yesterday:

https://community.powerbi.com/t5/Service/Refresh-error-when-using-NestedJoin/td-p/979690 

I tried to add a gateway anyway, but then unlike what you mentioned above, besides adding gateway cluster, I had to add a sub cluster under the first one, and authentication fails no matter what I used.

Do you have any idea how to solve this?  Thank you so much in advance.

Best regards,

David

[Anonymous]

thank you @edhans  adding the gateway setting has worked.