# 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) Power Query and M Intensive Training Course £1,050.00 – £26,550.00 Select optionsContinue Loading Done Power Query and M Introduction £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Intermediate £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Advanced £395.00 – £9,750.00 Select optionsContinue Loading Done Upcoming Courses Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```