The M Code Behind the Power Query M function Embedded.Value

What is Power Query M?

Power Query M is a functional language used in Power Query. It is used to perform data transformations and create custom functions. M is a case-sensitive language that uses a combination of expressions and values to perform calculations and data manipulations. The Embedded.Value function is one of the built-in functions in Power Query M.

Understanding the Embedded.Value Function

The Embedded.Value function is used to extract values from cells in an Excel sheet. It takes two arguments: the first argument is a cell reference, and the second argument is an optional default value. If the cell contains a value, the function returns that value. If the cell is empty, the function returns the default value. Here is the syntax for the Embedded.Value function:

Embedded.Value(cellReference, defaultValue)

The cellReference argument can be a reference to a single cell or a range of cells. The defaultValue argument is optional, and if it is not specified, the function returns a blank value if the cell is empty.

How the Embedded.Value Function Works

Let's take a deeper look at how the Embedded.Value function works. Suppose you have an Excel sheet with the following data:

| Customer Name | Sales |


| John | $100 |

| Jane | |

| Bob | $200 |

| Sarah | $300 |

You can use the Embedded.Value function to extract the sales data for Jane. Here's an example of how to use the function:

= Embedded.Value([Sales][[2]])

In this example, [Sales][[2]] is the cell reference for the second row in the Sales column. Since there is no value in the cell, the function returns a blank value.

Now, let's say you want to return a default value of $0.00 if the cell is empty. Here's how you would modify the function:

= Embedded.Value([Sales][[2]], “$0.00”)

In this example, the function will return $0.00 since the cell is empty.

The Embedded.Value function is an important function in Power Query M that allows you to extract values from cells in an Excel sheet. By understanding the M code behind this function, you can perform more advanced data transformations and create custom functions to suit your business needs.

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

Upcoming Courses

10-12 Jul 23 (London or Online)
25-27 Sep 23 (London or Online)
20-22 Nov 23 (London or Online)

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)