Edit

Merge or append on-premises and cloud data sources

  • Article

Note

We've split the on-premises data gateway docs into content that's specific to Power BI and general content that applies to all services that the gateway supports. You're currently in the Power BI content. To provide feedback on this article, or the overall gateway docs experience, scroll to the bottom of the article.

You can use the on-premises data gateway to merge or append on-premises and cloud data sources in the same query. This solution is helpful when you want to combine data from multiple sources without having to use separate queries.

Note

This article applies only to datasets that have cloud and on-premises data sources merged or appended in a single query. For datasets that include separate queries, for instance, one that connects to an on-premises data source and the other to a cloud data source, the gateway doesn't execute the query for the cloud data source.

Prerequisites

  • A gateway installed on a local computer.
  • A Power BI Desktop file with queries that combine on-premises and cloud data sources.

Note

To access any cloud data sources, you must ensure that the gateway has access to those data sources.

  1. In the upper-right corner of the Power BI service, select the gear icon then Manage connections and gateways.

    Screenshot shows the gear icon with its context menu with Manage connections and gateways selected.

  2. Select the gateway you want to configure, and select Settings from the top ribbon

    Screenshot of opening the setting page for gateways.

  3. Under Settings, select Allow user's cloud data sources to refresh through this gateway cluster, then select Save.

  4. To add any on-premises data sources used in your queries, select Connections, then select New to create a connection. You don't need to add the cloud data sources here.

  5. Select your gateway for Gateway cluster name. Name the connection and specify the type of connection and other required information. Then select Create.

  6. Upload to the Power BI service your Power BI Desktop file with the queries that combine on-premises and cloud data sources.

With the cloud credentials set, you can now refresh the dataset by using the Refresh now option. Or, you can schedule it to refresh periodically.

Related content

To learn more about data refresh for gateways, see Use the data source for scheduled refresh.