Table.ReplaceErrorValues

Updated on

Table.ReplaceErrorValues is a Power Query M function that replaces error values in specified columns with new values provided in a list. The function returns a new table with the error values replaced according to the errorReplacement list.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Description

Table.ReplaceErrorValues replaces error values in specified columns with corresponding values. It changes error values in table columns with new values in errorReplacement list, formatted as {{column1, value1}, …}. One replacement per column is allowed.

Examples

Replace the error value with the text “world” in the table.

Table.ReplaceErrorValues( 
    Table.FromRows( {{1, "hello"}, {3, ...}}, {"A", "B"} ),
    {"B", "world"}
 )

 /* Output: 
Table.FromRecords( {
    [A = 1, B = "hello"],
    [A = 3, B = "world"]
} )
 */ 

Replace the error value in column A with the text “hello” and in column B with the text “world” in the table.

Table.ReplaceErrorValues( 
    Table.FromRows( {{..., ...}, {1, 2}}, {"A", "B"} ),
    {{"A", "hello"}, {"B", "world"}}
 )

 /* Output: 
Table.FromRecords( {
    [A = "hello", B = "world"],
    [A = 1, B = 2]
} )
 */ 

Other functions related to Table.ReplaceErrorValues are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-replaceerrorvalues