Table.ReplaceErrorValues

Substitute the inaccurate values in the designated columns of the table with the updated values found in the errorReplacement list. The errorReplacement list should be in the format of {{column1, valu...

Syntax

Table.ReplaceErrorValues(table as table, errorReplacement as list) as table

About

Substitute the inaccurate values in the designated columns of the table with the updated values found in the errorReplacement list. The errorReplacement list should be in the format of {{column1, value1}, ...}. There should only be one replacement value per column; attempting to specify the same column multiple times will lead to an error.

Explanation

Example 1: Substitute the erroneous value with the term "world" within the specified table using the following Power Query M function. Usage: Power Query MTable.ReplaceErrorValues( Table.FromRows({{1, "hello"}, {3, ...}}, {"A", "B"}), {"B", "world"}) Output: Table.FromRecords({ [A = 1, B = "hello"], [A = 3, B = "world"]}) Example 2: Replace the incorrect values in column A with "hello" and in column B with "world" within the given table by executing the following Power Query M operation. Usage: Power Query MTable.ReplaceErrorValues( Table.FromRows({{..., ...}, {1, 2}}, {"A", "B"}), {{"A", "hello"}, {"B", "world"}}) Output: Table.FromRecords({ [A = "hello", B = "world"], [A = 1, B = 2]})