Expression.Constant

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Expression.Constant Function

The Expression.Constant function is a built-in function in Power Query that allows users to create a constant value. The syntax for the function is as follows:

Expression.Constant(value as any) as function

The function takes a single argument, value, which can be any data type. The function then returns a function that, when called, returns the value passed as an argument.

For example, the following M code creates a constant value of 42:

constantValue = Expression.Constant(42)

The constantValue variable now contains a function that, when called, will return the value 42.

Using the Expression.Constant Function

The Expression.Constant function can be used in a variety of ways in Power Query. One common use case is to define a constant value that can be reused throughout a query. For example, if we wanted to calculate the average price of a product across multiple regions, we could use the following M code:

pricePerRegion = Table.Group(products, {“Region”}, {{“Average Price”, each List.Average([Price])}})

In this code, we are grouping the products table by the Region column and calculating the average price for each region. However, we may want to calculate the average price across all regions as well. We could do this by defining a constant value for the total number of regions and using it in our calculation:

totalRegions = Expression.Constant(4)

pricePerRegion = Table.Group(products, {“Region”}, {{“Average Price”, each List.Average([Price])}})

averagePrice = List.Sum(Table.Column(pricePerRegion, “Average Price”)) / totalRegions

In this code, we have defined a constant value of 4 for the total number of regions and used it to calculate the average price across all regions.

The Expression.Constant function is a powerful tool for creating constant values in Power Query. By understanding the M code behind the function, users can take advantage of its flexibility and create more efficient and effective queries. Whether defining a constant value for reuse throughout a query or using it in more complex calculations, the Expression.Constant function is an essential tool for any Power Query user.

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)