Connecting to your On-premises data from Power Query Online
Power BI DesktopPower Query Online
As you may know, about half of all corporate data is still stored on-premises based on a recent survey from Statista. While Power Query is helping empower data users to connect to, reshape, and clean data from various sources – how can it do so if your data is in your on-premises network secured by a firewall? By using an On-premises data gateway.
An On-premises data gateway provides secure connectivity from cloud services like Power Query Online, Power BI, Power Apps and other services to your on-premises data sources like SQL Server, Teradata, SAP, local files and a long list of others. In addition to enabling connectivity, a data gateway is an excellent example of edge computing – it hosts the mashup engine, bringing compute closer to your data source thus reducing bandwidth delays and improving response times.
Beyond on-premises data sources, data gateways can also be used to connect to cloud data sources with IP restrictions. For instance, you can use a data gateway to connect securely to data services within Azure VNets.
On-premises data gateways
Data gateways provide you the capability to centrally manage access to your enterprise data sources. Gateway Admins in a company create data source definitions on the gateway. These data sources are then shared with citizen developers within the enterprise who can create reports, dataflows or apps using these data sources.
Data gateway clusters offer high availability to make sure query evaluations succeed even if a gateway node fails. Load balancing is also available so in addition to scaling up, you can scale out to ensure many concurrent queries can be executed without causing a resources crunch.
The same gateway can be used with multiple services like Power BI, Azure Analysis Services, Power Apps, Power Automate, Dataflows in Power BI & Power Apps, Logic Apps and others.
For in-depth information on data gateways, visit our architecture documentation at On-premises data gateway architecture | Microsoft Docs.
Use On-premises data gateways in Power Query Online.
In Power Query Online, to connect to an on-prem data sources for instance SQL Server, select the SQL connector and then provide data source details. You can then choose from a list of gateways – these are either gateways which you are an admin of or the ones on which you have access to at least one data source.
Once you select a gateway and the authentication mode, you can now connect to the data source and bring in data in the Power Query Online editor for transformations, merge or join with other cloud or on-premises data sources and more. You can then save this query and refresh it via the gateway for up-to-date data.
Get started with Data gateways.
These resources will help you learn more about on-premises data gateways, its setup and configurations, and new data gateway features we are working on:
What is an on-premises data gateway? | Microsoft Docs
Install an on-premises data gateway | Microsoft Docs
Power Query Connectors supported on a gateway | Microsoft Docs
Gateways Release Plan and Notes
Gateways Best Practices – Microsoft Business Applications Summit
Feedback
The Power Query team is interested in your feedback! Feel free to leave a comment below with your thoughts.