Connections and authentication in Power Query Online

In Power Query Online, a connection refers to the unique identifier and associated credentials used to establish a connection to a particular data source. One convenient feature of connections in Power Query is that you can create multiple connections against the same data source with different credentials.

There are two categories of connections:

Connection category name	Icon	Description
Cloud		Any connection for data source that's cloud-based and doesn't use a gateway.
On-premises		Any connection that requires a gateway.

Creating a connection

During the get data experience in Power Query Online, you'll find a dialog where you enter information to create and establish a connection to your data source. The process is standard for all connectors in Power Query, but some connectors might require more information in order to create a connection.

Creating a cloud connection

Using the Azure SQL Server database connector as an example, you can enter the connector settings to establish a connection. For the Azure SQL Server connector, the required setting is just the server name, but you can also enter the name of the database and select any other advanced options available for the connector.

After entering the values for the connector settings in the Connection settings section, you can proceed with the Connection credentials section. In this section, you can create a connection specific to the connection settings you previously entered.

The following table contains the fields and values used in the Connection settings section.

Field	Description	Sample value
Connection Name	The name you can enter for your new connection.	Sample Connection
Data Gateway	An optional field that lets you bind a gateway to your connection. For cloud connections, there's no gateway binding to the connection.	none
Authentication Kind	The authentication kind you select to use that's supported by the connector.	Organizational account
Credentials	Depending on the authentication kind you select, there will be available a contextual set of fields to input your credentials, a button to launch an OAuth2 flow, or even no fields at all for an authentication kind such as Anonymous.	Derived from OAuth2 flow, but is shown as a Sign in button in the image

Note

By default, the field for the connection name tries to provide a default name when you create a connection using the information from the connection settings.

After finishing the Connection settings section, you select the Next button to move forward in the get data experience.

Tip

Some connectors provide an auto sign in experience. To learn more about this feature, go to auto sign in.

Creating an on-premises connection

As an alternative, you can also bind a gateway to your connection, which converts your connection from a cloud connection to an on-premises connection.

Note

To create a gateway you can read the article on using an on-premises data gateway in dataflows.

Using a local SQL Server database as an example, you enter the connector settings to establish a connection. For the SQL Server connector, the required setting is just the server name, but you can also enter the name of the database and select any other advanced options available for the connector. For demonstration purposes, both the server name and database have been entered.

After entering the values for the connector in Connection settings, you can proceed with the Connection credentials section. In this section, you can create a connection specific to the connection settings you previously entered.

The following table contains the fields and values used in the Connection settings section.

Field	Description	Sample value
Connection Name	The name you can enter for your new connection	localhost;AdventureWorks2019
Data Gateway	An optional field that lets you bind a gateway to your connection.	Mike Test
Authentication Kind	The authentication kind you select to use that's supported by the connector.	Basic
Credentials	Depending on the authentication kind selected, there will be available a contextual set of fields to input your credentials, a button to launch an OAuth2 flow, or even no fields at all for an authentication kind such as Anonymous.	Username and password

After finishing the Connection settings section, you select the Next button to move forward within the get data experience.

Components of a connection

Each connection is made up of a set of components. The following table contains more information for each component.

Component name	Required or optional	Description	Sample
Data Source kind	Required	The data source for which the connection is being established.	SQL Server, File, Folder, Azure Data Lake Storage
Data Source path	Required	A string that represents the values or parameters used to establish a connection to your data source.	Server Name, Database Name
Authentication	Required	Encompasses both the authentication method and the credentials used for authentication.	Windows, OAuth2, Anonymous
Gateway	Optional	Used when a gateway is needed to establish the connection and execute any query evaluation.	Any gateway
Privacy level	Optional	Establishes the security for each connection, which is taken into consideration when queries from different connections are combined.	None, Public, Organizational, Private

Important

Currently, the privacy level is set to None for any new connections created. When you try to combine multiple data sources, a new dialog prompts you to define the data privacy levels of the data sources that you want to combine.

Known connections

When Power Query recognizes a set of connection settings, it tries to look up in its respective credentials storage to see if there's a connection that matches those settings and, if so, automatically selects that connection.

To override this behavior, you can take either of the following two actions:

• Display the dropdown menu to scan a list of available connections for the given connection settings. You can then select the one that you'd like to use or create a new one.

  

• Select Edit connection to modify the existing connection or select Create new connection from the dropdown menu to create a new named connection.

  

More resources

• Microsoft Entra ID client IDs
• List of connectors in Power Query
• On-premises data gateways documentation
• Change the gateway used in a dataflow
• Troubleshooting dataflow issues: Connection to the data source