Odbc.DataSource
Accessing DataReturns a navigation table of tables from an ODBC data source using the given connection string.
Syntax
Odbc.DataSource(connectionString as any, optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
connectionString | any | Yes | An ODBC connection string as text (e.g., "Driver={SQL Server};Server=myserver;Database=mydb;") or a record of connection string key-value pairs. |
options | record | No | An optional record with options such as CreateNavigationProperties, HierarchicalNavigation, HideNativeQuery, SqlCompatibleWindowsAuth, CommandTimeout, ConnectionTimeout, and TolerateConcatOverflow. |
Return Value
table — A navigation table of schemas and tables available through the ODBC data source.
Remarks
Odbc.DataSource connects to any data source that has an ODBC driver installed on the local machine and returns a navigation table of the available schemas and tables. It is a generic connector of last resort — useful for databases that do not have a dedicated Power Query connector (such as IBM DB2, Teradata, Sybase ASE, Informix, SAP Hana via ODBC, etc.) or for connecting to proprietary systems that expose an ODBC interface.
Connection string formats: The connectionString parameter accepts two forms:
- A text DSN or driver string: "DSN=MyDSN;" to use a pre-configured System DSN, or "Driver={DriverName};Server=host;Database=db;" for a driver-based connection.
- A record of key-value pairs: [Driver="SQL Server", Server="myserver", Database="mydb"]. Power Query assembles these into a connection string automatically.
Authentication: Database credentials (UID and PWD) can be embedded in the connection string for text-format connection strings, but this is not recommended for security reasons. Prefer configuring credentials in the Power Query data source credentials dialog, which securely stores them outside the M query. For Windows authentication, use Trusted_Connection=yes where supported by the driver.
Driver requirements: The ODBC driver for the target data source must be installed on the machine running Power Query. Since Power BI Desktop is a 64-bit application, you must install the 64-bit ODBC driver. For 32-bit Excel, install the 32-bit driver. Verify driver availability in Windows' ODBC Data Source Administrator (odbcad32.exe). Power BI Service does not support ODBC without an on-premises data gateway.
Query folding: Folding capability depends on the individual ODBC driver. Drivers that correctly report their SQL grammar support via ODBC capabilities APIs will enable folding. If folding is not working as expected, use Odbc.Query to execute SQL directly and control what runs on the server.
Key options:
- CreateNavigationProperties (logical) — generate navigation properties from foreign key constraints if the driver supports metadata queries.
- HierarchicalNavigation (logical) — group the navigation table by schema name.
- HideNativeQuery (logical) — prevents native SQL pass-through for security hardening.
- CommandTimeout (duration) — maximum time to wait for a query to complete.
- ConnectionTimeout (duration) — maximum time to wait when establishing the connection.
Examples
Example 1: Connect using a pre-configured System DSN
```powerquery
Odbc.DataSource("DSN=MyDataSource;")Example 2: Connect using a driver connection string
```powerquery
Odbc.DataSource("Driver={PostgreSQL Unicode};Server=myserver;Port=5432;Database=analytics;")Example 3: Connect using a record of key-value pairs
```powerquery
Odbc.DataSource([
Driver = "SQL Server",
Server = "myserver",
Database = "Northwind"
])Example 4: Navigate to a specific schema and table
```powerquery
let
Source = Odbc.DataSource("DSN=MyDB;"),
Schema = Source{[Name="dbo"]}[Data],
OrdersTable = Schema{[Name="Orders"]}[Data]
in
OrdersTable