ODBC
Item | Description |
---|---|
Release State | General Availability |
Products | Excel Power BI (Semantic models) Power BI (Dataflows) Fabric (Dataflow Gen2) Power Apps (Dataflows) Dynamics 365 Customer Insights Analysis Services |
Authentication Types Supported | Database (Username/Password) Windows Default or Custom |
Function Reference Documentation | Odbc.DataSource Odbc.Query |
Note
Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
Before you get started, make sure you've properly configured the connection in the Windows ODBC Data Source Administrator. The exact process here depends on the driver.
- Import
- Advanced options
- Connection string (non-credential properties)
- SQL statement
- Supported row reduction clauses
To make the connection, take the following steps:
Select the ODBC option in the Get Data selection.
In From ODBC, select the data source name (DSN) from the Data source name (DSN) drop-down box. In this example, a DSN name of SQL Server Database is used.
You can also choose Advanced options to enter more optional connection information. More information: Connect using advanced options
Once you're done, select OK.
If this is the first time you're connecting to this database, select the authentication type and input your credentials when prompted.
The authentication types available are:
- Default or Custom: Select this authentication type when you don't specify any credentials if you're using DSN configured with a username and password. Or, if you need to include credentials as connection string properties.
- Windows: Select this authentication type if you want to connect using Windows authentication. Optionally, include any connection string properties you need.
- Database: Select this authentication type to use a username and password to access a data source with an ODBC driver. Optionally, include any connection string properties you need. This is the default selection.
More information: Authentication with a data source
Once you're done, select Connect.
In the Navigator, select the database information you want, then either select Load to load the data or Transform Data to continue transforming the data in Power Query Editor.
To make the connection, take the following steps:
From the Data sources page, select ODBC.
In the ODBC page, enter your ODBC connection string. In the following example, the connection string is
dsn=SQL Server Database
.If needed, select an on-premises data gateway in Data gateway.
Choose the authentication kind to sign in, and then enter your credentials.
Select Next.
In the Navigator, select the database information you want, and then select Transform data to continue transforming the data in Power Query Editor.
Power Query provides a set of advanced options that you can add to your query if needed.
Advanced option | Description |
---|---|
Connection string (non-credential properties) | Provides an optional connection string that can be used instead of the Data source name (DSN) selection in Power BI Desktop. If Data source name (DSN) is set to (None), you can enter a connection string here instead. For example, the following connection strings are valid: dsn=<myDSN> or driver=<myDriver>;port=<myPortNumber>;server=<myServer>;database=<myDatabase>;. To escape special characters, use { } characters. Keys for connection strings vary between different ODBC drivers. Consult your ODBC driver provider for more information about valid connection strings. |
SQL statement | Provides a SQL statement, depending on the capabilities of the driver. Ask your vendor for more information, or go to Import data from a database using native database query. |
Supported row reduction clauses | Enables folding support for Table.FirstN. Select Detect to find supported row reduction clauses, or select from one of the drop-down options (TOP, LIMIT and OFFSET, LIMIT, or ANSI SQL-compatible). This option isn't applicable when using a native SQL statement. Only available in Power Query Desktop. |
If a DSN is specified in the ODBC connection string, the attributes after the DSN specification aren't included. If you want to use additional attributes, update them in the DSN itself, as opposed to in the connection string.