Value.ReplaceType

Understanding the Value.ReplaceType Function

Before we dive into the M code behind Value.ReplaceType, let’s briefly review how the function works. The syntax for Value.ReplaceType is as follows:

The function takes two arguments: the value to be converted and the new data type. For example, if you wanted to convert a column containing text values to a numeric data type, you could use the following code:

This would replace the data type of the column from text to numeric.

Breaking Down the M Code

Now that we understand how Value.ReplaceType works, let’s take a closer look at the M code behind it. The code for Value.ReplaceType can be found in the following file:

The code for the function is as follows:

Let’s break this down line by line:

1. `(Value as any, Type as type) =>` – This line defines the two arguments for the function: Value (the value to be converted) and Type (the new data type).

2. `let` – This line specifies that we are creating a new variable called “Result” that will store the output of the function.

3. `Result = try Value as Type otherwise null` – This line attempts to convert the value to the new data type. If the conversion is successful, Result will store the converted value. If not, Result will be null.

4. `in` – This line specifies that the code following it will use the variable “Result”.

5. `if Result = null then Value else Result` – This line checks to see if the conversion was successful. If it was, the converted value (stored in “Result”) is returned. If not, the original value (stored in “Value”) is returned.

Use Cases for Value.ReplaceType

Now that we understand the M code behind Value.ReplaceType, let’s explore some of its use cases. Here are a few examples:

Converting Text to Numbers

As mentioned earlier, one common use case for Value.ReplaceType is converting text to numeric data types. This is useful when working with data sources that store numbers as text (such as CSV files).

Converting Dates to Date/Time Values

Another use case for Value.ReplaceType is converting dates (stored as text) to date/time values. This is useful when working with data sources that store dates in a non-standard format.

Converting Text to Boolean Values

Value.ReplaceType can also be used to convert text values to Boolean values. This is useful when working with data sources that store Boolean values as text (such as “True” and “False”).

In this article, we’ve explored the M code behind the Power Query M function Value.ReplaceType. We’ve seen how this function can be used to replace the data type of a column in a query, and we’ve explored some of its use cases. Whether you’re working with CSV files, non-standard date formats, or text-based Boolean values, Value.ReplaceType can help you quickly and easily convert your data to the correct data type.

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

Upcoming Courses

Contact Us