Table.ReplaceErrorValues

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

Suppose you have a table named Source in Power Query that contains data rows, but some cells include errors. Specifically, there are:

• Errors in the ProductKey column
• Errors in the OrderDate column

Here’s what the table looks like:

Replacing Error Values in Specific Columns

The Table.ReplaceErrorValues function in Power Query lets you replace error values in one or more columns with specified default values. This is helpful when you want to fill error cells with meaningful information or default data that maintains consistency.

For instance, let’s say you want to:

• Replace errors in the ProductKey column with a default value of 1.
• Replace errors in the OrderDate column with the default date of December 31, 2024.

You can accomplish this by specifying the column names along with the replacement values, as shown below:

Table.ReplaceErrorValues(
  Source, 
  {
    { "ProductKey", 1 }, 
    { "OrderDate", #date( 2024, 12, 31 ) }
  }
)

After running this function, the table will look like this, with errors replaced by the specified default values:

Try it yourself

To see this in action, you can try the following code by pasting it into Power Query’s Advanced Editor:

let
    Source = #table(
    type table[ProductKey = Int64.Type, Product = Text.Type, OrderDate = Date.Type, Sales = Int64.Type ],
    {
        {1/"e", "Product A", #date(2024, 1, 15),     150 },
        {2,     "Product B", #date(2024, 2, 20) + 4, 200 },
        {3,     "Product C", #date(2024, 3, 10),     175 },
        {4,     "Product D", #date(2024, 4, 25),     220 },
        {5,     "Product E", #date(2024, 5, 5 ),     185 },
        {6,     "Product F", #date(2024, 6, 15),     240 }
    }
),
  ReplaceErrorsInColumns = 
    Table.ReplaceErrorValues(
      Source, 
      {
        {"ProductKey", 1}, 
        {"OrderDate", #date(2024, 12, 31)}
      }
    )
in
  ReplaceErrorsInColumns

Related functions

Other functions related to Table.ReplaceErrorValues are:

• Table.RemoveRowsWithErrors
• Table.SelectRowsWithErrors

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