List.ReplaceMatchingItems

D

Back to top

T

Back to top

#

Back to top

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

One of the most powerful functions in the M language is List.ReplaceMatchingItems. This function allows users to replace all occurrences of a particular value in a list with a new value. In this article, we will take a closer look at the M code behind this function and explore some of its use cases.

Understanding List.ReplaceMatchingItems

Before we dive into the M code behind List.ReplaceMatchingItems, let’s first take a moment to understand what the function does. List.ReplaceMatchingItems is used to replace all occurrences of a particular value in a list with a new value. The function takes three arguments:

1. The list to be modified.

2. The value to be replaced.

3. The new value that will replace all occurrences of the original value.

Here’s an example of how the function can be used:

let

myList = {1, 2, 3, 4, 5, 3, 6, 7},

replacedList = List.ReplaceMatchingItems(myList, 3, 9)

in

replacedList

This code will replace all occurrences of the value 3 in the list {1, 2, 3, 4, 5, 3, 6, 7} with the value 9. The resulting list will be {1, 2, 9, 4, 5, 9, 6, 7}.

The M Code Behind List.ReplaceMatchingItems

Now that we understand what List.ReplaceMatchingItems does, let's take a closer look at the M code behind the function. Here's the M code for List.ReplaceMatchingItems:

(list as list, oldValue as any, newValue as any) as list =>

List.Transform(

list,

each if _ = oldValue then newValue else _

)

Let's break this code down line by line:

(list as list, oldValue as any, newValue as any) as list =>

This line defines the function parameters. The function takes a list, an old value, and a new value as input and returns a list as output.

List.Transform(

list,

each if _ = oldValue then newValue else _

)

This line is where the actual work is done. List.Transform is a built-in function in the M language that takes two arguments: a list and a function that will be applied to each element of the list. In this case, the function being applied is an if statement that checks if the current element of the list is equal to the old value. If it is, the new value is returned. If it is not, the original value is returned.

Use Cases for List.ReplaceMatchingItems

List.ReplaceMatchingItems can be used in a wide range of scenarios when working with data in Power Query. Here are a few examples:

Replacing Values in a Column

Suppose you have a column in a table that contains a particular value that needs to be replaced with a new value. You can use List.ReplaceMatchingItems to replace all occurrences of the old value in the column with the new value. Here's an example:

let

Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],

#”Replaced Value” = Table.ReplaceValue(Source,”Old Value”,”New Value”,Replacer.ReplaceValue,{“Column1”})

in

#”Replaced Value”

This code replaces all occurrences of "Old Value" in the "Column1" column of a table named "Table1" with "New Value".

Transforming Data in a List

List.ReplaceMatchingItems can also be used to transform data within a list. For example, suppose you have a list of dates in the format "MM/DD/YYYY" that you need to convert to the format "YYYY-MM-DD". You can use List.ReplaceMatchingItems to transform all occurrences of "/" to "-". Here's an example:

let

myList = {“01/01/2020”, “02/01/2020”, “03/01/2020”},

transformedList = List.ReplaceMatchingItems(myList, “/”, “-“)

in

transformedList

This code replaces all occurrences of "/" in the list {"01/01/2020", "02/01/2020", "03/01/2020"} with "-". The resulting list will be {"01-01-2020", "02-01-2020", "03-01-2020"}.

List.ReplaceMatchingItems is a powerful function in the M language that enables users to replace all occurrences of a particular value in a list with a new value. The function is easy to use and can be applied to a wide range of scenarios when working with data in Power Query. By understanding the M code behind List.ReplaceMatchingItems, users can gain a deeper understanding of how the function works and how it can be customized to meet their specific needs.

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)