Table.ReplaceValue

D

Back to top

T

Back to top

#

Back to top

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

What is the Table.ReplaceValue Function?

The Table.ReplaceValue function is used to replace specific values in a table or column with new values. It has the following syntax:

Table.ReplaceValue(table as table, old_value as any, new_value as any, optional replacer as nullable function) as table

The function takes four parameters:

– `table` – The table or column where the replacement should occur.

– `old_value` – The value that should be replaced.

– `new_value` – The new value that should replace the old value.

– `replacer` – (optional) A function that should be used to generate the new value. This function takes two parameters: the old value and the new value.

The function returns a new table or column with the specified values replaced.

How to Use the Table.ReplaceValue Function?

To use the Table.ReplaceValue function, you need to follow these steps:

1. Load the data into Power Query.

2. Select the table or column where the replacement should occur.

3. Go to the Transform tab and click on the Replace Values button.

4. In the Replace Values dialog box, enter the old value and the new value.

5. Click on OK to replace the specified values.

Alternatively, you can use the M language to write the code for the replacement. Here’s an example:

let

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

ReplaceValue = Table.ReplaceValue(Source,"old_value","new_value",Replacer.ReplaceText,{"Column1"})

in

ReplaceValue

In this example, the code replaces the value “old_value” with “new_value” in the column “Column1” of the table.

Understanding the M Code Behind the Table.ReplaceValue Function

The M code behind the Table.ReplaceValue function is quite simple. Here’s how it works:

1. First, the function takes the table or column where the replacement should occur as an input parameter.

2. Next, it searches for all occurrences of the old value in the specified table or column.

3. For each occurrence, it replaces the old value with the new value.

4. Finally, it returns the new table or column with the specified values replaced.

The M code for the Table.ReplaceValue function looks like this:

let

ReplaceValue = (table as table, old_value as any, new_value as any, replacer as nullable function) =>

let

replace = (value as any) =>

if value = old_value then

if replacer <> null then

replacer(value, new_value)

else

new_value

else

value,

replaceAll = Table.TransformColumns(table, {{"Column1", each replace(_), type text}})

in

replaceAll

in

ReplaceValue

In this code, the `ReplaceValue` function takes the four input parameters and returns a new table or column with the specified values replaced. The `replace` function is used to replace individual values, and the `replaceAll` function is used to replace all occurrences of the old value in the specified table or column.

The Table.ReplaceValue function is an essential tool for data transformation and manipulation in Power Query. It allows you to replace specific values in a table or column with new values, making it easier to work with data and prepare it for analysis. By understanding the M code behind this function, you can build more complex and powerful transformations that meet your 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)