Value.Is

What is the Value.Is function?

The Value.Is function is used to check whether a value is of a certain type. This function takes two arguments: the value you want to check and the type you want to check for. It returns a boolean value: True if the value is of the specified type, False if it is not.

For example, the following formula checks whether the value in column “Value” is a number:

If the value in the “Value” column is a number, the “IsNumber” column will contain a True value. If the value is not a number, the “IsNumber” column will contain a False value.

How the Value.Is function works

The Value.Is function is actually a wrapper function that calls other functions depending on the type you want to check for. These functions are called type functions.

For example, if you want to check whether a value is a number, the Value.Is function calls the Type.IsNumber function. If you want to check whether a value is a text string, the Value.Is function calls the Type.IsText function.

The M code behind the Value.Is function is actually quite simple. Here is the code for the Value.Is function:

Let’s break down this code:

– The function takes two arguments: Value and Type.

– The “as any” syntax means that the Value argument can be of any data type.

– The “as type” syntax means that the Type argument must be a data type.

– The function uses the try otherwise syntax to attempt to call the Type.Value function on the Value argument. If this call succeeds, the function returns True. If the call fails (i.e. Value is not of the specified type), the function returns False.

Using the Value.Is function

The Value.Is function can be used in a variety of ways to make your data analysis more efficient. Here are just a few examples:

Checking for specific data types

As mentioned earlier, the most common use of the Value.Is function is to check whether a value is of a specific data type. Here are some examples:

This formula returns True, because 42 is a number.

This formula returns True, because “hello” is a text string.

Filtering data

The Value.Is function can also be used to filter data. For example, if you have a table of data and you only want to keep rows where a certain column contains a number, you can use the Value.Is function like this:

This formula returns a new table that only contains rows where the “MyColumn” column contains a number.

Handling errors

The Value.Is function can also be used to handle errors. For example, if you are importing data from a file and you want to make sure that a particular column contains only numbers, you can use the Value.Is function like this:

This formula attempts to transform the “MyColumn” column to a number data type. If this operation fails (i.e. the column contains non-numeric values), the formula returns an error message.

The Value.Is function is a powerful tool in Power Query that allows you to check whether a value is of a certain type. By understanding the M code behind this function, you can use it in a variety of ways to make your data analysis more efficient. Whether you are checking for specific data types, filtering data, or handling errors, the Value.Is function is an essential part of any data analyst’s toolkit.

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

Upcoming Courses

Contact Us