Odbc.Query

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Odbc.Query function

The Odbc.Query function is a Power Query M function that allows users to connect to any data source that has an ODBC driver installed. The function takes two arguments: the connection string and the SQL query. The connection string specifies the details of the ODBC data source, such as the server name, database name, username, and password. The SQL query specifies the data that needs to be retrieved from the data source.

Here is an example of the Odbc.Query function in action:

let

connectionString = “dsn=SampleDB;uid=sa;pwd=Password123;”,

query = “SELECT FROM Customers”

in

Odbc.Query(connectionString, query)

In this example, we are connecting to a data source named SampleDB using the ODBC driver. We are then retrieving all the data from the Customers table using the SQL query "SELECT FROM Customers".

The M code behind the Odbc.Query function

The M code behind the Odbc.Query function is relatively simple. Here is the code:

let

ODBCDataSource = Odbc.DataSource(connectionString),

ODBCQuery = Odbc.Query(ODBCDataSource, query)

in

ODBCQuery

In this code, we first create a variable named ODBCDataSource, which is set to the Odbc.DataSource function with the connection string as the argument. This function returns a record that contains the details of the ODBC data source.

We then create a variable named ODBCQuery, which is set to the Odbc.Query function with the ODBCDataSource record and the SQL query as the arguments. This function returns a table that contains the data retrieved from the data source.

Finally, we return the ODBCQuery table as the output of the function.

Using the Odbc.Query function with different data sources

One of the great things about the Odbc.Query function is that it can be used to connect to and retrieve data from a wide range of data sources. Here are some examples of how the function can be used with different data sources:

SQL Server

To connect to a SQL Server database, you can use the following connection string:

Server=;Database=;Uid=;Pwd=;

Here is an example of a SQL Server connection string:

Server=MyServer;Database=MyDatabase;Uid=sa;Pwd=Password123;

MySQL

To connect to a MySQL database, you can use the following connection string:

Driver={MySQL ODBC 8.0 Unicode Driver};Server=;Database=;User=;Password=;Option=3;

Here is an example of a MySQL connection string:

Driver={MySQL ODBC 8.0 Unicode Driver};Server=localhost;Database=mydatabase;User=myusername;Password=mypassword;Option=3;

Oracle

To connect to an Oracle database, you can use the following connection string:

Driver={Oracle in OraClient18Home1};Dbq=:/;Uid=;Pwd=;

Here is an example of an Oracle connection string:

Driver={Oracle in OraClient18Home1};Dbq=myserver:1521/mydatabase;Uid=myusername;Pwd=mypassword;

PostgreSQL

To connect to a PostgreSQL database, you can use the following connection string:

Driver={PostgreSQL Unicode};Server=;Port=5432;Database=;Uid=;Pwd=;SSLmode=prefer;ReadOnly=0;

Here is an example of a PostgreSQL connection string:

Driver={PostgreSQL Unicode};Server=myserver;Port=5432;Database=mydatabase;Uid=myusername;Pwd=mypassword;SSLmode=prefer;ReadOnly=0;

The Odbc.Query function in Power Query is a powerful tool that allows users to connect to and retrieve data from a wide range of data sources. By understanding the M code behind the function, users can customize the function to fit their specific needs and connect to any data source that has an ODBC driver installed.

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)