Merge or append on-premises and cloud data sources

Is this page helpful?

Note

We recently revised the on-premises data gateway docs, splitting them into Power BI specific content 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.

The on-premises data gateway enables you to merge or append on-premises and cloud data sources in the same query. This 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, which include separate queries - one connecting to an on-premises and the other to a cloud data source - the query using the cloud data source won't be executed using the gateway.

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 Settings gear icon > Manage gateways.

    Manage gateways

  2. Select the gateway you want to configure.

  3. Under Gateway Cluster Settings, select Allow user's cloud data sources to refresh through this gateway cluster > Apply.

    Refresh through this gateway cluster

  4. Under this gateway cluster, add any on-premises data sources used in your queries. You don't need to add the cloud data sources here.

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

  6. On the Dataset settings page for the new dataset:

    • For the on-premises source, select the gateway associated with this data source.

    • Under Data source credentials, edit the cloud data source credentials as necessary.

      Dataset settings

    Make sure privacy levels for both cloud and on-premises data sources are set appropriately to ensure the joins are handled securely.

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

Next steps

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