List.ReplaceValue

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.ReplaceValue

Understanding the List.ReplaceValue Function

Before we dive into the code behind the List.ReplaceValue M function, let’s first understand what the function is all about. List.ReplaceValue is a function that enables you to replace values in a list with new values. The syntax for the function is as follows:

List.ReplaceValue(list as list, old_value as any, new_value as any, optional occurrence as nullable number) as list

The List.ReplaceValue function takes four arguments. The first argument is the list that you want to modify. The second argument is the old value that you want to replace. The third argument is the new value that you want to replace the old value with. The fourth argument is optional, and it specifies the number of occurrences of the old value that you want to replace. If you omit the fourth argument, all occurrences of the old value in the list will be replaced.

The M Code Behind the List.ReplaceValue Function

Now that we have a basic understanding of the List.ReplaceValue function, let's take a closer look at the M code that powers the function. The M code for List.ReplaceValue is as follows:

(list as list, old_value as any, new_value as any, optional occurrence as nullable number) =>

let

replaceValue = (value) => if value = old_value then new_value else value,

replaceAll = List.Transform(list, replaceValue),

replaceOccurrences = if occurrence <> null then List.PositionalReplace(replaceAll, old_value, new_value, occurrence) else replaceAll

in

replaceOccurrences

Let's break down the code step-by-step to understand what's happening. The first line of the code defines the function and its arguments. The second line defines a new function called replaceValue, which is used to replace the old value with the new value. The third line applies the replaceValue function to every item in the list using the List.Transform function. The fourth line checks if the occurrence argument is specified. If it is, the List.PositionalReplace function is used to replace the specified number of occurrences of the old value with the new value. If the occurrence argument is not specified, all occurrences of the old value are replaced.

Examples of Using List.ReplaceValue

Let's look at some examples of how you can use the List.ReplaceValue function to replace values in a list.

Example 1: Replacing All Occurrences of a Value

Suppose you have a list of numbers, and you want to replace all occurrences of the number 2 with the number 4. You can use the List.ReplaceValue function as follows:

let

numbers = {1, 2, 3, 2, 5},

replaceAll2sWith4 = List.ReplaceValue(numbers, 2, 4)

in

replaceAll2sWith4

The output of this query will be {1, 4, 3, 4, 5}.

Example 2: Replacing a Specific Occurrence of a Value

Suppose you have a list of names, and you want to replace the second occurrence of the name "John" with the name "Mike". You can use the List.ReplaceValue function as follows:

let

names = {“Alice”, “John”, “Bob”, “John”, “Charlie”},

replaceSecondJohnWithMike = List.ReplaceValue(names, “John”, “Mike”, 2)

in

replaceSecondJohnWithMike

The output of this query will be {"Alice", "John", "Bob", "Mike", "Charlie"}.

In conclusion, the List.ReplaceValue function is a powerful tool that enables you to replace values in a list with new values. The M code behind the function is relatively simple, but it can be modified to suit your specific needs. By understanding how the List.ReplaceValue function works, you can use it to extract insights from your data and transform it in meaningful ways.

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)