Expression.Constant - Expression Function

Expression.Constant is a Power Query M function that generates the M source code representation of a constant value. The function returns a text value representing the M code.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Expression.Constant( value as any ) as text

Description

The Expression.Constant function outputs a constant value in its original code format. This function is useful for embedding values within expressions, ensuring they are represented accurately in the code.

Supported constants are:

Null
Logical
Number
Time
Date
DateTime
DateTimeZone
Duration
Text

Examples

Let’s look at some examples of the Expression.Constant function. This function becomes useful when you want to concatenate different values.

Concatenating Numbers with Strings

When you need to include a numerical value within a string, you typically convert the number to a string. Using Expression.Constant, you can achieve the same. For example:

// Output: "I am 32 years old."
"I am " & Expression.Constant( 32 ) & "years old."

We could have achieved something similar by applying the Text.From or Number.ToText function. However, in some situation it is easier to use this function instead.

Handling Quotes in Strings

Inserting quotes within a string can be tricky, as you need to manage the number of quotations correctly. Consider the following code, which requires multiple quotes:

// Output: "Here is "a ""double"" quote" in a string"
"Here is ""a """"double"""" quote"" in a string"

With Expression.Constant, you can simplify this process. Simply pass the output you want to see in the string to the function:

= "Here is " & Expression.Constant( "a ""double"" quote" ) & " in a string"

Using Expression.Constant with Numbers

If you want to represent a number as a constant text value, you can use Expression.Constant directly:

// Output: "2024"
Expression.Constant( 2024 )

To verify that the output is indeed text, you can use the Value.Type function:

Value.Type( Expression.Constant( 2024 ) ) // Output: type text

Documentation Purposes

Expression.Constant is also useful for representing complex values as text in documentation. For example:

// Output: "#duration( 20, 10, 3, 0 )"
Expression.Constant( #duration( 20, 10, 3, 0 ) )

Know that you can perform operations within the first argument of Expression.Constant. The function will evaluate the operation first and then return the result as text. Consider the following example:

// Output: "#datetime( 2024, 5, 31, 6, 3, 0 )"
Expression.Constant( #datetime( 2024, 5, 10, 20, 0, 0 ) + #duration( 20, 10, 3, 0 ) )

In this case, #datetime(2024, 5, 10, 20, 0, 0) + #duration(20, 10, 3, 0) is evaluated to produce a datetime value. The Expression.Constant function then returns this value as text.

Other functions related to Expression.Constant are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/expression-constant