Replace values

We can use the Replace Values feature in Power Query Editor to replace any value with another value in a selected column.

Step 1: Select the column header that contains the value that we want to replace, and then select Replace Values on the Transform tab.

Or right click on the column header, in which we want replace the value.

Step 2: In the Value to Find box, enter the name of the value that we want to replace, and then in the Replace With box, enter the correct value name and then select OK. In Power Query, we can't select one cell and change one value, like we might have done in Excel.

Replace null values We can replace null values in our data with the other values. If the value stays null, the averages will not calculate correctly. One solution would be to change the nulls to zero, which will produce the more accurate average. In this instance, replace the null values with zero.

Replace the blank values with null We can replace the values with null. In the Replace values dialog box, leave Value to find blank. For Replace with, enter null.

Advanced Options The replace values operation has two modes:

• Replace entire cell contents: This is the default behavior for non-text columns, where Power Query searches for and replaces the full contents of a cell. You can enable this mode for text columns by selecting Advanced options, and then selecting the Match entire cell contents check box.
• Replace instances of a text string: This is the default behavior for text columns, where Power Query will search for a specific text string in all rows of a column and replace as many instances of the text string that it finds.

Advanced options are only available in columns of the Text data type. Within that set of options is the Replace using special characters option.