Sql.Database

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Sql.Database

Connecting to a SQL Server Database

Before we dive into the M code behind the Sql.Database function, let’s first take a look at how to connect to a SQL Server database using Power Query.

1. Open Power Query and click on the “From Database” dropdown in the “Get Data” section of the ribbon.

2. Select “From SQL Server Database” and enter the server name and database name that you want to connect to.

3. If you need to specify any additional options, such as authentication or a specific query, you can do so in the “Advanced options” section.

4. Click on “Connect” and Power Query will connect to your SQL Server database and retrieve a list of tables.

Once you have connected to your SQL Server database, you can start using the Sql.Database function to access data.

The M Code Behind the Sql.Database Function

The Sql.Database function is a part of the M language used in Power Query. It allows you to connect to a SQL Server database and retrieve data using a SQL query. Here is the basic syntax of the Sql.Database function:

Sql.Database(server as text, database as text, optional options as nullable record) as table

Let’s break down each part of the function:

– `server`: The name of the SQL Server instance that you want to connect to.

– `database`: The name of the database that you want to connect to.

– `options`: An optional record that allows you to specify additional options, such as authentication or a specific query.

– `table`: The table that contains the data retrieved from the SQL Server database.

Here is an example of how to use the Sql.Database function to retrieve data from a SQL Server database:

let

Source = Sql.Database("localhost", "AdventureWorks2019"),

dbo_Products = Source{[Schema="dbo",Item="Products"]}[Data]

in

dbo_Products

In this example, we are connecting to a SQL Server database named “AdventureWorks2019” on the “localhost” server. We then retrieve the “Products” table from the “dbo” schema and store it in the “dbo_Products” variable.

Using the Sql.Database Function with Parameters

In addition to the basic syntax, the Sql.Database function also allows you to specify parameters in your SQL query. This can be useful when you need to filter or group your data based on certain criteria. Here is an example of how to use parameters in the Sql.Database function:

let

Source = Sql.Database("localhost", "AdventureWorks2019"),

Query = "SELECT FROM dbo.Products WHERE Color = @Color",

dbo_Products = Source{[Schema="dbo",Item=Query,Parameters=[Color="Red"]]}[Data]

in

dbo_Products

In this example, we are retrieving the “Products” table from the “dbo” schema, but we are only selecting rows where the “Color” column is equal to “Red”. We do this by specifying a parameter in our SQL query using the ‘@’ symbol, and then passing in the value for that parameter in the “Parameters” record.

The Sql.Database function is a powerful tool that allows you to connect to a SQL Server database and retrieve data using a SQL query. By understanding the M code behind the function, you can customize your queries and retrieve the data that you need for your analysis. Whether you are a beginner or an experienced user, the Sql.Database function can help you get the most out of your data 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)