Expression.Evaluate

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Expression.Evaluate

The Expression.Evaluate function in Power Query M language is one such function that allows users to evaluate a string expression as a formula. This function is a powerful tool that can be used to create dynamic formulas, perform calculations on the fly, and manipulate data in complex ways.

Understanding the Expression.Evaluate Function

The Expression.Evaluate function in Power Query M language takes a single argument, which is a string expression that contains a formula. The function then evaluates the string expression as a formula and returns the result.

Here’s an example that shows how the Expression.Evaluate function works:

Expression.Evaluate(“1 + 2 + 3”)

The above formula will evaluate to 6, which is the sum of 1, 2, and 3. This is a simple example, but it illustrates the power of the Expression.Evaluate function.

Using Expression.Evaluate to Create Dynamic Formulas

The Expression.Evaluate function can be used to create dynamic formulas that change based on the data in a table. Here's an example:

Expression.Evaluate(“if [Sales] > 1000 then [Sales] 0.1 else [Sales] 0.05”)

In the above example, the formula uses an if statement to determine whether the value in the Sales column is greater than 1000. If it is, the formula multiplies the value by 0.1; otherwise, it multiplies the value by 0.05. This is a dynamic formula that changes based on the data in the Sales column.

Manipulating Data with Expression.Evaluate

The Expression.Evaluate function can also be used to manipulate data in complex ways. For example, you can use the function to split a column into multiple columns based on a delimiter.

Here's an example:

Expression.Evaluate(“Table.SplitColumn([Column1], “”;””, {“”Column2″”, “”Column3″”})”)

In the above example, the formula splits the data in the Column1 column into two columns, Column2 and Column3, based on the ";" delimiter. This is a powerful data manipulation technique that can be used to transform data in unique ways.

The Expression.Evaluate function in Power Query M language is a powerful tool that can be used to create dynamic formulas and manipulate data in complex ways. By understanding how this function works, you can unlock the full potential of Power Query and create more sophisticated data transformations.

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)