Odbc.Query
Accessing DataReturns a table from an ODBC data source by executing the specified SQL query.
Syntax
Odbc.Query(connectionString as any, query as text, optional options as nullable record) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
connectionString | any | Yes | An ODBC connection string as text (e.g., "DSN=MyDSN;") or a record of connection string key-value pairs. |
query | text | Yes | The SQL query to execute against the data source. |
options | record | No | An optional record with options such as CommandTimeout and ConnectionTimeout. |
Return Value
table — A table containing the results of the SQL query executed against the ODBC data source.
Remarks
Odbc.Query executes a SQL statement directly against an ODBC data source and returns the result as a Power Query table. Unlike Odbc.DataSource, which provides a navigation table for interactive browsing and supports Power Query-generated SQL folding, Odbc.Query sends the SQL you write verbatim to the ODBC driver. Use it when you need precise control over the SQL — for example, when using database-specific syntax, calling stored procedures, or running queries that Power Query's automatic folding cannot express.
Odbc.Query vs Odbc.DataSource: Choose Odbc.Query when the SQL is known in advance and is fixed or only changes via parameters. Choose Odbc.DataSource when you want Power Query to generate and fold SQL dynamically based on the transformations you apply in the query editor.
Query folding: Odbc.Query results are not further foldable. The SQL is executed on the server exactly as written; any Power Query transformations applied after Odbc.Query are performed in-memory in the Power Query engine. If you need downstream folding, use Odbc.DataSource and navigate to the table instead.
Connection string formats: Accepts the same formats as Odbc.DataSource — either a text DSN/driver string ("DSN=MyDSN;" or "Driver={DriverName};Server=...;") or a record of key-value pairs ([Driver="SQL Server", Server="myserver", Database="mydb"]).
Driver requirements: The appropriate ODBC driver must be installed on the machine running Power Query. Power BI Desktop requires a 64-bit driver. Power BI Service does not support ODBC without an on-premises data gateway.
Key options:
- CommandTimeout (duration) — maximum time to wait for the query to complete before raising a timeout error.
- ConnectionTimeout (duration) — maximum time to wait when establishing the initial connection.
Security note: Avoid embedding credentials (UID=, PWD=) directly in the connection string in the M query. Use the Power Query credentials dialog to store credentials securely outside the query code.
Examples
Example 1: Run a query using a pre-configured System DSN
```powerquery
Odbc.Query("DSN=MyDataSource;", "SELECT * FROM Orders WHERE Status = 'Open'")Example 2: Run a query using a driver connection string
```powerquery
Odbc.Query(
"Driver={PostgreSQL Unicode};Server=myserver;Database=sales;",
"SELECT product_id, SUM(quantity) AS total_sold FROM order_lines GROUP BY product_id ORDER BY total_sold DESC"
)Example 3: Use a record for the connection string
```powerquery
Odbc.Query(
[Driver = "SQL Server", Server = "myserver", Database = "Northwind"],
"SELECT TOP 100 OrderID, CustomerID, OrderDate FROM Orders ORDER BY OrderDate DESC"
)Example 4: Set a command timeout for a long-running query
```powerquery
Odbc.Query(
"DSN=DataWarehouse;",
"SELECT * FROM FactSales WHERE SaleDate >= '2024-01-01'",
[CommandTimeout = #duration(0, 0, 10, 0)]
)