Edit

Share via

OLE DB

  • Article

Summary

Item Description
Release State General Availability
Products Excel
Power BI (Semantic modelss)
Analysis Services
Authentication Types Supported Exchange account
Microsoft account
Function Reference Documentation Exchange.Contents

Note

Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.

Capabilities Supported

  • Import

Connect to OLE DB from Power Query Desktop

Power Query Desktop includes Excel, Power BI (semantic models), and Analysis Services as experiences.

To make the connection to OLE DB, follow these steps:

  1. From Get Data, select the Other category, select OLE DB, and then select Connect. More information: Where to get data

    Screenshot of the Get Data dialog, showing OLE DB selected.

  2. In the OLE DB window that appears, enter your connection string. Optionally, you can provide other SQL query information in the Advanced options tab.

    Tip

    Avoid using credential or security properties in this connection string, as you'll enter them in the next menu.

    Screenshot of the OLE DB dialog, showing connection string entered as Provider=SQLOLEDB;Data Source=(local);InitialCatalog=Adventureworks.

  3. Select OK.

  4. Choose the kind of authentication you'd like to use: Default or Custom, Windows, or Database.

    • Default or Custom: Enter custom or security related connection string properties, things like security strings or passwords that shouldn't be added to a query. For example 'UID=MyUserId; RefreshToken=MySecretRefreshToken; Encryption=true'
    • Windows: Sign in with your current windows credentials, or provide a different Windows username and password. You can also provide any other connection string properties, if needed.
    • Database: Provide your database username and password. You can also provide any other connection string properties, if needed.

    Screenshot of the OLE DB dialog, showing username, password, and optional connection string properties entered.

  5. In Navigator, review and select data from your database. Then select either Load to load the table, or Transform Data to open the Power Query Editor where you can filter and refine the set of data you want to use, and then load that refined set of data.