PowerQuery - Dynamic Text Replacement with Table.ReplaceValue

By Lukas Hillesheim, 4 April, 2023
Blog Article & Copyright by Lukas Hillesheim
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:
Figure 3 - PowerQuery Code
Figure 3 - PowerQuery 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