# Power Query: Extra Value

20 March 2019

*Welcome to our Power Query blog. This week, I look at some more Value M functionality. *

* *

Last week Power Query: Value Added, I looked at the **M** functions **Value.Is()** and **Value.FromText()**. This week, I will look at additional **M** functionalities that use **Value()**. To start, I begin with

Value.As(**value **as any, **type** as type) as any

where **value** is the data being analysed and **type** is the data type being tested for.

In the Microsoft help pages, **Value.As()** is described as follows:

**Value.As** is the function corresponding to the **as** operator in the formula language. The expression **value as type** asserts that the value of a **value** argument is compatible with **type** as per the **is** operator. If it is not compatible, an error is raised.

The difference between **Value.As()** and **Value.Is()** is that the latter returns a logical, or Boolean (TRUE / FALSE) result, whereas the former either returns the value, or issues an error. I can demonstrate the difference in the following example:

The **M** code I have used for **Value.As()** is:

= Value.As(Value.FromText([Column1]), type number))

I have taken a mixed data column and tested for whether the data is a number. **Value.As()** has issued an error when the data cannot be a number, and returned the number when it is a valid number. In contrast, **Value.Is()** has returned TRUE for a valid number and FALSE if the data cannot be a valid number. Therefore, **Value.As()** could be useful when I want to remove rows that do not contain a valid number (by choosing the ‘Remove Errors’ option from the ‘Remove Rows’ section of the ‘Home’ tab).

Let’s move on. I will now consider

Value.Add(**value1** as any, **value2 **as any, optional **precision** as nullable number) as any

where **value1** and **value2** are the values to be added, and the **precision** the values are considered to can be specified.

There are of course many ways of adding and merging columns, an example of how to use **Value.Add()** is shown on the next screenshot:

A quirk of **Value.Add()** is that it doesn’t deal with nulls:

This can be solved by replacing nulls with zeros before applying the **Value.Add()** functionality. Although **Value.Add()** will work for two values of type **any**, a result will only be given for valid numbers, otherwise an error is issued.

Next up is

Value.Divide(**value1** as any, **value2** as any, optional **precision** as nullable number) as any

where **value1** and **value2** are the values to be added, and the **precision** the values are considered to can be specified.

This is another way to divide one value by a second value:

Finally, for this week:

Value.Equals(**value1** as any, **value2** as any, **equater** as record) as logical

where **value1** and **value2** are the values to be compared. The optional **equater** means that the user can define the conditions where the values would be equal.

Comparing values is often useful in calculations, and **Value.Equals()** is a logical function, but in this case not a Boolean operator, since more information is returned. If the first value is greater than the second value (according to the rules supplied in the **equater** *(sic)*, if present), then 1 is returned. If the values are the same, zero (0) is returned, and if the first value is less than the second value, -1 is returned.

As the next example shows, text and nulls can also be compared:

The characters in text values are compared by their position in the alphabet.

Come back next time for more ways to use Power Query!