SqlExpression.ToExpression

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function SqlExpression.ToExpression

What is SqlExpression.ToExpression?

The SqlExpression.ToExpression function is used to convert a SQL expression into a Power Query expression. This function is useful when you need to perform data transformations that involve SQL expressions. The function takes a SQL expression as input and returns a Power Query expression that represents the same logic as the SQL expression.

How does SqlExpression.ToExpression work?

The SqlExpression.ToExpression function works by parsing the SQL expression and generating the equivalent Power Query expression. Let’s take a look at an example to understand how this works.

Suppose we have a SQL expression that looks like this:

SELECT FirstName, LastName, Email

FROM Customers

WHERE Age > 30

To convert this SQL expression into a Power Query expression using the SqlExpression.ToExpression function, we would use the following M code:

let

sqlExpression = "SELECT FirstName, LastName, Email FROM Customers WHERE Age > 30",

pqExpression = SqlExpression.ToExpression(sqlExpression)

in

pqExpression

This M code first defines the SQL expression as a string variable called sqlExpression. It then passes this variable to the SqlExpression.ToExpression function to generate the equivalent Power Query expression. The resulting Power Query expression is stored in a variable called pqExpression.

Understanding the generated M code

The M code generated by the SqlExpression.ToExpression function can be quite complex, especially for more complex SQL expressions. Let’s take a closer look at the M code generated by the function for the example SQL expression we used earlier.

let

Source = Sql.Database("server", "database"),

dbo_Customers = Source{[Schema="dbo",Item="Customers"]}[Data],

FilteredRows = Table.SelectRows(dbo_Customers, each ([Age] > 30)),

SelectColumns = Table.SelectColumns(FilteredRows,{"FirstName", "LastName", "Email"})

in

SelectColumns

The M code generated by the SqlExpression.ToExpression function is essentially a Power Query script that performs the same operations as the original SQL expression. The script first connects to the SQL database using the Sql.Database function and then selects the Customers table from the dbo schema. It then filters the rows based on the Age column being greater than 30 and selects only the FirstName, LastName, and Email columns.

The SqlExpression.ToExpression function is a powerful tool that allows users to convert SQL expressions into Power Query expressions. This function is useful when you need to perform data transformations that involve SQL expressions. The M code generated by the SqlExpression.ToExpression function can be quite complex, but it provides a powerful way to perform complex data transformations using 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)