PowerQuery - Dynamic Text Replacement with Table.ReplaceValue

The function Table.ReplaceValue replaces values in one or more table columns. Lambda expressions provide a way for dynamic replacement.

Table.ReplaceValue(
    <arg1_table>,
    <arg2_lambda_expression: old_value>,
    <arg3_lambda_expression: new_value>,
    <arg4_lambda_expression: replacer>,
    <arg5_columns_to_search>)

In the following example, the English names of product categories are to be replaced by German ones.

English category names:

Figure 1 - English product categories — Figure 1 - Product Category on English

Goal: Translation into German category names:

Figure 2 - German Product Categories — Figure 2 - Product Category on German

Code:

Steps:

The iterator Table.ReplaceValue processes each row of table table2 item by item
Argument 2 and argument 3 have access to each column of the current row through the row lambda variable
For each row, the iterator passes the values of arguments 2, 3, and 5 to the lambda variables of argument 4:
- The value from the Category column is passed to the text lambda variable
- The value from the lambda expression / argument 2 is passed to the lambda variable oldValue
- The value from the lambda expression / argument 3 is passed to the newValue lambda variable
The value from the lambda expression / argument 4 is written to the Category column