Power Query can connect to many different data sources so you can work with the data you need. This article walks you through the steps for bringing in data to Power Query.
Connecting to a data source with Power Query follows a standard set of stages before landing the data at a destination. This article describes each of these stages.
In some cases, a connector might have all of these stages, and in other cases a connector might have just a few of them. For more information about the experience of a specific connector, visit the documentation available for the connector.
The set of steps or stages is:
1. Connection Settings
Most connectors initially require at least one parameter to initialize a connection to the data source. For example, the SQL Server connector requires at least the hostname to establish a connection to a SQL Server.
In comparison, when trying to connect to an Excel file, Power Query requires the user to find the file they want to connect to by using the file path.
The connector parameters are commonly used to establish a connection to a data source and they, in conjunction with the connector used, define what's called a data source path.
Some connectors don't require you to enter any parameters at all. These are called singleton connectors and will only have one data source path available per environment. Some examples are Adobe Analytics, MailChimp, and Google Analytics.
Every single connection that is made in Power Query has to be authenticated. The authentication methods vary from connector to connector, and some connectors might offer multiple methods of authentication.
The currently available methods of authentication for Power Query are:
- Anonymous—Commonly used when connecting to a data source that doesn't require user authentication, such as a web page, a file available over public HTTP, and so on.
- Basic—Accepts a username and password to be sent in base64 encoding.
- API Key—Accepts a single API Key for authentication.
- Organizational account / Microsoft Account—Also known as OAuth 2.0.
- Windows—Can be implicit or explicit.
- Database—Only available in some database connectors.
For example, the available authentication methods for the Azure SQL Server connector are Windows, Database, and Microsoft account.
3. Data preview
The goal of this data preview stage is to provide you with a user-friendly way to preview and select your data.
Depending on the connector that you're using, you can get one of two available experiences:
- Navigator window
- Table preview dialog
Navigator window (navigation table)
The Navigator window consists of two main sections:
- Object selection—Displayed on the left-hand side of the window. The
user can interact with these objects and select them.
For Power Query in Excel, you need to enable the multi-select option from the top left corner of the navigation window.
- The data preview—Displays a preview of the data from the selected object on the right-hand side of the window.
Table preview dialog
The Table preview dialog is a simple window that consists of only one section for the data preview. An example of a connector that provides this experience and window is the Folder connector.
4. Query destination
This is the phase where you define where to load the query. The options vary from integration to integration. But one option that is always available is the option to load data to the Power Query Editor to further transform and enrich the query.