Value.Add

At its core, Value.Add is a simple function that takes two arguments: the column or table to which the value will be added, and the value to add. The function then returns a new column or table with the value added. Here is the basic syntax for using Value.Add:

Where `table` is the table to which the value will be added, `column` is the name of the column to which the value will be added, and `value` is the value to add.

But what’s really going on behind the scenes when you use Value.Add? Let’s take a closer look at the M code that powers this function.

The M Code Behind Value.Add

Value.Add is implemented in M code, which is the language used by Power Query to define data transformations. The M code for Value.Add is relatively simple:

Let’s break this code down into its component parts.

The Function Signature

The first line of the code defines the function signature:

This line tells us several things about the function:

– The function is named `Value.Add`.

– The function takes three arguments: `table`, `column`, and `value`.

– The `table` argument must be of type `table`.

– The `column` argument must be of type `text`.

– The `value` argument can be of any type.

The `=>` operator at the end of the line indicates that the function returns a value of type `table`.

The Function Body

The rest of the M code defines the function body:

This code is wrapped in a `let` expression, which allows us to define local variables that can be used within the expression. In this case, we define two variables: `addValue` and `newTable`.

#### The addValue Variable

The `addValue` variable is a function that takes a row as input and returns a new row with the `column` field set to the `value` argument. Here is the code for the `addValue` function:

The `Record.AddField` function is used to add a new field to a record (which is essentially a row in a table). In this case, we use it to add the `value` argument to the `column` field of the row.

#### The newTable Variable

The `newTable` variable is created by calling the `Table.TransformRows` function. This function takes a table and a function as arguments, and applies the function to each row of the table. In this case, we pass the `addValue` function as the argument, which means that each row of the table will be transformed by adding the `value` argument to the `column` field.

The resulting table is then assigned to the `newTable` variable, which is returned as the output of the function.

Value.Add is a powerful function in Power Query that allows you to add a value to a column or table with ease. Behind the scenes, this function is implemented in M code, which provides a flexible and powerful framework for data transformation.

Understanding the M code behind functions like Value.Add can help you to use them more effectively, and to create custom functions that meet your specific needs. With its combination of simplicity and power, Power Query and M code are essential tools for anyone working with data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us