AdoDotNet.DataSource

D

T

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

Overview of AdoDotNet.DataSource

AdoDotNet.DataSource is a Power Query M function that allows users to connect to various data sources using .NET data providers. The function takes two arguments, the first being a connection string that specifies the data source, and the second being a query that retrieves the data from the source.

The function uses the .NET Framework’s System.Data.OleDb.OleDbConnection class to connect to the data source. This class provides a way to connect to a variety of data sources, including Microsoft Access, Excel, SQL Server, and Oracle.

Syntax of AdoDotNet.DataSource

The syntax of AdoDotNet.DataSource is as follows:


AdoDotNet.DataSource(connectionString as text, query as text) as table


The first argument, connectionString, is a text value that specifies the connection string to the data source. The second argument, query, is a text value that specifies the query to retrieve data from the data source. The function returns a table that contains the retrieved data.

Examples of AdoDotNet.DataSource

Here are some examples of how AdoDotNet.DataSource can be used:

Example 1: Connect to a SQL Server database


let

connectionString = "Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;",

query = "SELECT FROM Customers",

Source = AdoDotNet.DataSource(connectionString, query)

in

Source


This example connects to a SQL Server database using the SQLNCLI11 provider. The connection string specifies the server address, database name, username, and password. The query retrieves all rows from the Customers table.

Example 2: Connect to an Excel file


let

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:myFoldermyExcelFile.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';",

query = "SELECT FROM [Sheet1$]",

Source = AdoDotNet.DataSource(connectionString, query)

in

Source


This example connects to an Excel file using the Microsoft.ACE.OLEDB.12.0 provider. The connection string specifies the file path and the extended properties of the Excel file. The query retrieves all rows from the Sheet1 worksheet.

AdoDotNet.DataSource is a powerful M function in Power Query that allows users to connect to various data sources using .NET data providers. It provides a flexible and efficient way to retrieve data from different sources and manipulate it using Power Query’s transformation capabilities. With the examples provided above, users can start leveraging this function in their data analysis and business intelligence projects.

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)