Odbc.DataSource

Odbc.DataSource is a Power Query M function that retrieves a table of SQL tables and views from an ODBC data source using a specified connection string. The function returns the table with control over options like navigation properties, hierarchical navigation, and timeouts.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Odbc.DataSource(
   connectionString as any,
   optional options as nullable record,
) as table

Description

The Odbc.DataSource function accesses SQL tables and views from an ODBC data source, using a specified connection string, which can be either text or a record of property value pairs. This function is useful for connecting to and querying ODBC databases with tailored settings in Power Query environments. The operation returns a table format, and users can customize its behaviour with an optional ‘options’ record. The record can contain the following fields:

• CreateNavigationProperties : A logical (true/false) that sets whether to generate navigation properties on the returned values (default is true).
• HierarchicalNavigation : A logical (true/false) that sets whether to view the tables grouped by their schema names (default is false).
• ConnectionTimeout : A duration that controls how long to wait before abandoning an attempt to make a connection to the server. The default value is 15 seconds.
• CommandTimeout : A duration that controls how long the server-side query is allowed to run before it is canceled. The default value is ten minutes.
• SqlCompatibleWindowsAuth : A logical (true/false) that determines whether to produce SQL Server-compatible connection string options for Windows authentication. The default value is true.

Other options record fields are only available through extensibility. One of them is the SqlCapabilities option which supports the LimitClauseKind.Type, which specifies the row reduction clauses you can use. The following options specify that the SQL dialect:

• LimitClauseKind.None: does not support a limit clause.
• LimitClauseKind.Top: supports a TOP specifier to limit the number of rows returned.
• LimitClauseKind.LimitOffset: supports LIMIT and OFFSET specifiers to limit the rows returned.
• LimitClauseKind.Limit: supports a LIMIT specifier to limit the number of rows returned.
• LimitClauseKind.AnsiSql2008: supports an ANSI SQL-compatible LIMIT N ROWS specifier to limit the number of rows returned.

There are, however, quite some other options that you can control. For completeness, you can read more about additional parameters.

Examples

Return the SQL tables and views from the provided connection string.

// Output: table
Odbc.DataSource( "dsn=your_dsn" )

Another example that specifies options, is:

Odbc.DataSource(
  "dsn = your_dsn",
  [ HierarchicalNavigation = true,
    SqlCapabilities = [ LimitClauseKind=LimitClauseKind.AnsiSql2008 ]
  ]
)

Related articles

Learn more about Odbc.DataSource in the following articles:

• ODBC Data Sources, The SqlCapabilities Option And Power Query/Power BI Data Refresh Performance
  The article highlights the impact of the “Supported row reduction clauses” option in Power Query for ODBC data sources on data refresh performance and the efficiency of the Power Query Editor. » Read more

Related functions

Other functions related to Odbc.DataSource are:

• Odbc.InferOptions
• Odbc.Query

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/odbc-datasource