Embedded.Value

Embedded.Value

D

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

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: