Odbc.InferOptions

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Odbc.InferOptions

What is the Odbc.InferOptions Function?

The Odbc.InferOptions function is a Power Query M function that allows users to get information about the data source and customize their queries accordingly. This function is used with ODBC (Open Database Connectivity) data sources, which are a type of database connectivity standard that enables different applications to communicate with each other using SQL (Structured Query Language).

InferOptions function analyzes the data structure of the ODBC data source and returns a record with the following options:

How to Use the Odbc.InferOptions Function?

The Odbc.InferOptions function can be used in two ways:

1. Automatically Generate a Query

By default, the Odbc.InferOptions function is used to automatically generate a query based on the data source. Users can simply select the ODBC data source from the Power Query Editor and click on “Edit” to open the query editor. Power Query will automatically generate a query using the Odbc.InferOptions function and display the data in the preview window.

2. Customize a Query

Users can also use the Odbc.InferOptions function to customize their queries according to the data source. In this case, users need to create a new query and manually add the Odbc.InferOptions function to the M code. The following is an example of how to use the Odbc.InferOptions function to extract data from an ODBC data source:

let

Source = Odbc.DataSource("dsn=ExampleDSN"),

Options = Odbc.InferOptions(Source),

CommandText = Options[CommandText],

Table = Options[Table],

Columns = Options[Columns],

MaxRows = Options[MaxRows],

Connection = Options[Connection],

Query = Odbc.Query(Source, CommandText, [MaxRows = MaxRows])

in

Query

The Odbc.InferOptions function is a powerful tool for data analysts working with ODBC data sources. It analyzes the data structure of the data source and returns valuable information that can be used to customize queries according to the data source. The function can be used to automatically generate a query or to customize a query according to specific requirements. Understanding the M code behind the Odbc.InferOptions function is essential for data analysts to use this function effectively in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)