OleDb.Query

D

Back to top

T

Back to top

#

Back to top

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

Behind the scenes, the OleDb.Query function is powered by M code, a functional programming language used by Power Query to perform data transformations. In this article, we will take a closer look at the M code behind the OleDb.Query function, and explore how it works to execute SQL queries against relational databases.

Understanding the OleDb.Query Function

Before we dive into the M code behind the OleDb.Query function, let’s first take a closer look at what this function does. The OleDb.Query function is used to execute SQL queries against relational databases, and returns the results of these queries as a table.

To use the OleDb.Query function, you will need to provide the following inputs:

– Connection: The connection to the database that you want to execute the query against.

– Query: The SQL query that you want to execute.

– Options: Additional options, such as specifying the encoding or specifying how null values should be handled.

Here is an example of how the OleDb.Query function might be used to execute a simple SQL query against a database:

let

Source = OleDb.Query(

“Provider=SQLNCLI11;Server=myServerinstance;Database=myDB;Trusted_Connection=yes;”,

“SELECT FROM myTable”,

[Encoding=1252, NullValueHandling=Ignore]

)

in

Source

In this example, we are connecting to a SQL Server database using the SQL Native Client provider, and executing a simple SELECT query against a table called myTable. We are also specifying that any null values should be ignored, and that the encoding should be set to 1252.

How the M Code Behind OleDb.Query Works

Now that we understand how the OleDb.Query function works, let's take a look at the M code behind this function. The M code behind OleDb.Query is responsible for connecting to the database, executing the SQL query, and returning the results as a table.

Here is an example of the M code behind the OleDb.Query function:

let

// Define the connection string

connectionString = “Provider=SQLNCLI11;Server=myServerinstance;Database=myDB;Trusted_Connection=yes;”,

// Define the query to be executed

query = “SELECT FROM myTable”,

// Define the options for the query

options = [Encoding=1252, NullValueHandling=Ignore],

// Connect to the database and execute the query

Source = OleDb.DataSource(connectionString, [Query=query, Implementation=”OleDb”, Options=options])

in

Source

As you can see, the M code behind OleDb.Query is relatively simple. It first defines the connection string, query, and options that were passed in as inputs. It then uses the OleDb.DataSource function to connect to the database and execute the query, passing in the query, implementation (which is set to "OleDb"), and options as arguments.

The OleDb.DataSource function is a built-in function in Power Query that is used to connect to various data sources. When called with the "OleDb" implementation, this function uses the OleDb provider to connect to a relational database and execute a query.

In conclusion, the M code behind the OleDb.Query function is responsible for connecting to a relational database, executing an SQL query, and returning the results of that query as a table. By understanding how this function works, you can gain a deeper understanding of how Power Query works to transform and analyze data.

If you are interested in learning more about Power Query and M code, there are a number of resources available online, including Microsoft's official documentation and various online tutorials and courses. With a little bit of practice and experimentation, you can become a Power Query expert and take your data analysis skills to the next level.

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)