Table.ReplaceErrorValues

What are Error Values?

In Power Query, error values are values that indicate that a formula or transformation has failed. Common error values include #N/A, #VALUE!, #REF!, and #DIV/0!. These error values can occur for a variety of reasons, such as when a formula references a non-existent cell or when a division by zero occurs. Error values can be problematic when working with large datasets, as they can cause downstream calculations and transformations to fail.

What is Table.ReplaceErrorValues?

Table.ReplaceErrorValues is a function provided by the M language that allows users to replace error values in a table with a specified default value. The function takes two arguments: the first argument is the input table, and the second argument is the default value to replace error values with. The function returns a new table with the same schema as the input table, but with error values replaced with the specified default value.

Here’s an example of how Table.ReplaceErrorValues works:

In this example, we have a table with two columns: Column1 and Column2. The second row of Column2 contains a null value, which is an error value. We want to replace this error value with the default value of 999. We use the Table.ReplaceErrorValues function to do this, passing in the input table (Source) and the default value (999). The function returns a new table (ReplacedErrors) with the same schema as the input table, but with the error value replaced with the default value.

How to Use Table.ReplaceErrorValues

To use Table.ReplaceErrorValues, you first need to have a table with error values that you want to replace. You then need to decide on a default value to replace the error values with. Once you have these two pieces of information, you can use the Table.ReplaceErrorValues function to perform the replacement.

Here’s an example of how to use Table.ReplaceErrorValues in a real-world scenario. Let’s say we have a table of sales data that includes a column for sales revenue and a column for sales tax. The sales tax column includes a formula that calculates the tax based on the revenue, but sometimes the formula fails and produces an error value. We want to replace any error values in the sales tax column with a default value of 0.

In this example, we use the Excel.CurrentWorkbook function to get the data from the SalesData table in the current workbook. We then use Table.ReplaceErrorValues to replace any error values in the sales tax column with a default value of 0. Finally, we output the resulting table.

Table.ReplaceErrorValues is a powerful function provided by the M language in Power Query that allows users to replace error values in a table with a specified default value. This function can be useful in a wide range of scenarios where error values are present in a dataset. By understanding how to use Table.ReplaceErrorValues, you can improve the quality and accuracy of your data transformations in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us