Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Merging tables from two different data sources

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

 

 

 

 

 

 

 

Tips and Tricks
  • edhans's avatar
    edhans
    7 years ago

    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.

3 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      thank you edhans  adding the gateway setting has worked.

    • primolee's avatar
      primolee
      Icon for Helper V rankHelper V
      6 years ago

      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