Power Query get data experience overview

Use Power Query to connect to data, select the tables or files you need, and prepare them for use. This overview explains the connection stages in Power Query Desktop and Power Query Online and helps you choose an Online get data module.

For product-specific entry points, such as the ribbon command in Excel or Dataflow Gen2, see Where to get data. For source-specific settings and supported authentication, use the connector documentation.

The stages differ between Power Query Desktop and Power Query Online. In Power Query Desktop, connection settings and authentication are separate stages, and after previewing data, you choose a query destination. In Power Query Online, connection settings and credentials are combined in a reusable connection, and selected data always opens in the Power Query editor before you save it.

Important

Not every connector uses every stage of the get data experience. For the steps required by a specific connector, see its documentation in Connectors in Power Query.

Power Query Desktop experience

The stages for getting data in the Power Query Desktop experiences are:

  1. Connection settings

  2. Authentication

  3. Data preview

  4. Query destination

Flow diagram illustrating the four stages of getting data.

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 host name to establish a connection to the SQL Server database.

Screenshot of the SQL Server connector parameters.

To connect to an Excel file, Power Query requires the file path to locate the file.

Use connector parameters to establish a connection to a data source. Together with the connector, these parameters define a data source path.

Note

Some connectors don't require any parameters. These connectors are called singleton connectors and have only one data source path available per environment. Some examples are Adobe Analytics, Mailchimp, and Google Analytics.

For a full list of available connectors and the products they support, see Connectors in Power Query.

2. Authentication

Power Query authenticates every connection. The authentication methods vary by connector, and some connectors offer multiple authentication methods.

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 webpage or a file available over public HTTP.
  • API Key: Power Query accepts a single API key for authentication.
  • Basic: Power Query accepts a username and password sent in base64 encoding.
  • Database: This method is only available in some database connectors.
  • Organizational account or Microsoft account: This method is also known as OAuth 2.0.
  • Service Principal: Uses Microsoft Entra ID for authentication.
  • Windows: Can be implicit or explicit.

For example, the available authentication methods for the SQL Server database connector are Windows, Database, and Microsoft account.

Screenshot of the SQL Server database connector authentication methods.

For information about how authentication works, how to change credentials, and how to troubleshoot authentication issues, see Connector authentication.

3. Data preview

The data preview stage lets you preview and select your data.

Depending on the connector that you use, you can preview data by using either:

  • Navigator window
  • Table preview dialog box

The Navigator window consists of two main sections:

  • The object selection pane appears on the left side of the window. You can interact with and select these objects.

    Note

    For Power Query in Excel, select the Select multiple items option from the upper-left corner of the Navigator window to select more than one object at a time in the object selection pane. The list of objects in Power Query Desktop is limited to 10,000 items. This limit doesn't exist in Power Query Online. For a workaround in Power Query Desktop, go to Object limitation workaround.

  • The data preview pane on the right side of the window shows a preview of the data from the object you selected.

    Screenshot of the SQL Server connector navigator window in Power Query Desktop.

Object limitation workaround

The Navigator in Power Query Desktop has a fixed limit of 10,000 objects. This limit doesn't apply to Power Query Online.

Use the following workaround:

  1. Right-click the root node of the Navigator, and then select Transform Data.

    Screenshot of the Navigator with transform data from the dropdown menu emphasized.

  2. The Power Query editor opens with the full navigation table in the table preview area. This view doesn't have a limit on the number of objects. Use filters or other Power Query transforms to find the rows you want, for example by filtering the Name column.

  3. When you find the item you want, you can access the contents by selecting the data link (such as the Table link in the following image).

    Selecting the Table link to view the contents.

Table preview dialog box

The table preview dialog box consists of only one section for the data preview. An example of a connector that provides this experience and window is the Folder connector.

Screenshot of the Folder connector table preview dialog box.

4. Query destination

At this stage, specify where to load the query. The available destinations depend on the Power Query Desktop product, but Transform data is always available and loads the data into the Power Query editor for further transformation.

Screenshots of the get data load destinations in navigator window and table preview.

Power Query Online experience

The stages for getting data in Power Query Online are:

  1. Connection settings and authentication

  2. Data preview

  3. Query editor

Flow diagram with the three stages of getting data in Power Query Online.

1. Connection settings and authentication

In Power Query Online, start at the Connect to data source page and enter values in two sections:

  • Connection settings

  • Connection credentials

    Screenshot of the Connect to data source page using the SQL Server database connector.

Connection settings

In the connection settings section, enter the information needed to connect to your data source. Depending on the connector, this information might include a server name, database name, folder path, or file path. Some connectors provide additional sections or advanced options for more control over the connection.

Screenshot of the Connect to data source dialog with the advanced options section expanded.

Connection credentials

The first time you use Power Query to connect to a specific data source, you need to create a connection for that source. A connection includes the gateway, credentials, privacy levels, and other connector-specific fields needed to connect. For a detailed walkthrough, see Connections and authentication in Power Query Online.

Note

Some connectors offer fields in the connection credentials section to define security for the connection. For example, the Amazon Redshift connector offers the Use Encrypted Connection field.

Connect to data source dialog with all information to create a new connection entered.

The main connection fields are:

  • Connection name: A unique name for your connection. You can't use a name that's already in your environment.
  • Data gateway: If your data source requires a data gateway, select it from the dropdown list. For details, see On-premises data gateways documentation.
  • Authentication kind and credentials: The available authentication kinds and credential fields depend on the connector. For example, when you select Windows, the Username and Password fields appear.
  • Privacy level: Set the privacy level for your data source to None, Private, Organizational, or Public). For details, see Privacy levels.

Important

Some Power Query integrations don't support a defined connection or a privacy level. Power Query Online experiences provide settings for the gateway, authentication kind, and credentials needed to connect to your source.

After you define a connection in Power Query Online, you can reuse it later without reentering the connection information. The Connection field offers a dropdown menu where you select a defined connection. After you select it, you don't need to enter any other details before selecting Next.

Connect to data source dialog box where the connection name was picked from the dropdown menu inside the connection field.

To use different credentials, a privacy level, a gateway, or other connector-specific settings in your project, select Edit connection. Under Connection, select Create new connection, then change the required fields.

Connect to data source dialog where the connection name was picked from the dropdown menu inside the connection field.

For more information about managing, editing, and reusing connections, see Manage connections.

2. Data preview

The data preview stage lets you preview and select your data.

Depending on the connector that you're using, you can preview data by using either:

  • Navigator window
  • Table preview dialog box

The Navigator window consists of two main sections:

  • The object selection pane appears on the left side of the window. You can interact with and select these objects.

  • The data preview pane on the right side of the window shows a preview of the data from the object you selected.

    Screenshot of the SQL Server connector navigator window in Power Query Online.

Table preview dialog box in Power Query Online

The table preview dialog box consists of only one section for the data preview. An example of a connector that provides this experience and window is the Folder connector.

Screenshot of the table preview dialog box.

3. Query editor

For Power Query Online, you must load the data into the Power Query editor. In the editor, you can further transform and enrich the query.

Screenshot of a sample query loaded into the Power Query editor

Get data modules in Power Query Online

Use the modules on the left navigation bar to choose a starting point for your task. Each guide explains its controls and what the action creates.

Module Use it to
Home Choose among shortcuts to new sources, recent data, and the catalog.
New data source Find a connector and configure a connection.
Recents & My Queries Return to recent or favorite items and reuse personal query scripts.
Templates Start from a template that matches your scenario.
OneLake catalog Find an existing Fabric data item and review its context before connecting.
New Fabric item Create a destination item when configuring where to write data.
Azure data sources Connect to Azure-hosted data sources through their connectors.
Upload file Upload a local file and create a query from it.
Blank table Enter or paste values into a new table.
Blank query Write or paste an M expression and choose a gateway context where supported.

Save and load your data

After you transform your data, use the product-specific command to save and load it: Close & Load in Excel, Close & Apply in Power BI Desktop, Save & close in Power Query Online, or Import or Close & Apply in Analysis Services.

In Excel, select Close & Load on the Home tab to save and load your changes.

Screenshot of the Close and Load option.

In Power BI Desktop, select Close & Apply on the Home tab to save and load your changes.

Screenshot of the Close and Apply option.

In Power Query Online, select Save & close in the lower-right corner of the editor to save and load your changes.

Screenshot of the Save & close button in Power Query Online.

If you open the Power Query editor in Analysis Services, you can save and load your changes by either using the Import button or the Home > Close & Apply option in the Power Query editor ribbon.

Screenshot of the Import button in Analysis Services.

Screenshot of the Close & Apply button in Analysis Services.

Browse and connect with new experiences

The following guides describe browsing and query experiences. Each guide explains how to select data and what happens when you connect.

Module Use it to
Browse Azure Choose preview or legacy instructions for finding Azure resources and connecting to their data.
SharePoint (Preview) Browse sites and folders to connect to a file or a file listing.
Copilot Explore and transform selected data with natural-language prompts.
Shared Query (Preview) Browse shared query logic and import independent copies.