OleDb.DataSource

D

Back to top

T

Back to top

#

Back to top

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

In this article, we’ll take a closer look at the M code behind the OleDb.DataSource function. We’ll explore its syntax, parameters, and options, and provide practical examples that demonstrate how to use it effectively.

Understanding OleDb.DataSource

Before we dive into the code behind this function, let’s briefly discuss the concept of OLE DB. OLE DB (Object Linking and Embedding, Database) is a Microsoft technology that provides a standard interface to access various data sources, including databases, spreadsheets, and flat files. By using an OLE DB connection, Power Query can communicate with different database systems, such as SQL Server, Oracle, and MySQL.

Now that we have a basic understanding of OLE DB, let’s move on to the OleDb.DataSource function. This function is used to connect to a database using an OLE DB connection. It takes several parameters, including the provider, server, database, and any required credentials.

Here’s the basic syntax of the OleDb.DataSource function:

OleDb.DataSource(connectionString as text, optional options as nullable record) as table

Let's break down each part of this syntax:

- connectionString: This is a required parameter that specifies the connection string used to connect to the database. The connection string contains information about the database provider, server, database name, and any necessary credentials. It must be provided as a text value.

- options: This is an optional parameter that allows you to specify additional options for the connection, such as the query timeout or the maximum number of rows to retrieve. These options are specified as a record value.

- table: This is the output of the function, which is a table containing the data retrieved from the database.

Using OleDb.DataSource in Practice

Now that we know the syntax and parameters of the OleDb.DataSource function, let's explore some practical examples of how to use it.

Example 1: Connecting to a SQL Server Database

Suppose you have a SQL Server database called "Sales" on a server named "Server1", and you want to connect to it using Power Query. Here's an example of how to use the OleDb.DataSource function to establish the connection:

let

connectionString = “Provider=SQLNCLI11;Server=Server1;Database=Sales;Trusted_Connection=yes;”,

source = OleDb.DataSource(connectionString),

salesData = source{[Schema=”dbo”,Item=”SalesData”]}[Data]

in

salesData

In this example, we first define the connection string, which specifies the provider, server, database name, and that we want to use a trusted connection (i.e., Windows authentication). We then use the OleDb.DataSource function to connect to the database and retrieve the "SalesData" table from the "dbo" schema.

Example 2: Specifying Connection Options

Suppose you have a large database with millions of rows, and you want to limit the number of rows returned by the query to improve performance. Here's an example of how to use the OleDb.DataSource function to specify the maximum number of rows to retrieve:

let

connectionString = “Provider=SQLNCLI11;Server=Server1;Database=Sales;Trusted_Connection=yes;”,

options = [CommandTimeout=120, MaxRows=10000],

source = OleDb.DataSource(connectionString, options),

salesData = source{[Schema=”dbo”,Item=”SalesData”]}[Data]

in

salesData

In this example, we add an options parameter to the OleDb.DataSource function, which specifies a maximum of 10,000 rows to retrieve from the database. This can help improve performance by reducing the amount of data that needs to be processed by Power Query.

Example 3: Using User Credentials

Suppose you need to connect to a database that requires specific user credentials, rather than using Windows authentication. Here's an example of how to use the OleDb.DataSource function to specify the user name and password:

let

connectionString = “Provider=SQLNCLI11;Server=Server1;Database=Sales;”,

options = [User ID=”username”, Password=”password”],

source = OleDb.DataSource(connectionString, options),

salesData = source{[Schema=”dbo”,Item=”SalesData”]}[Data]

in

salesData

In this example, we add an options parameter to the OleDb.DataSource function, which specifies the user name and password required to connect to the database. This allows you to connect to databases that require specific credentials, rather than relying on Windows authentication.

In conclusion, the OleDb.DataSource function is a powerful tool for connecting to databases using an OLE DB connection. By understanding its syntax, parameters, and options, you can effectively leverage this function to connect to various database systems, retrieve data, and transform it using Power Query. Whether you're working with SQL Server, Oracle, or MySQL, the OleDb.DataSource function can help streamline your data analysis workflow and improve your productivity.

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)