With Power Query, you can replace one value with another value wherever that value is found in a column. The Replace values command can be found:
On the cell shortcut menu. Select and hold (or right-click) the cell to replace the selected value in the column with another value.
On the column shortcut menu.
On the Home tab, in the Transform group.
On the Transform tab, in the Any column group.
The replace values operation has two modes:
Replace entire cell contents: This mode is the default behavior for nontext columns, where Power Query searches for and replaces the full contents of a cell. You can enable this mode for text columns by selecting the Advanced option in the Replace values dialog, and then selecting the Match entire cell contents check box.
Replace instances of a text string: This mode is the default behavior for text columns. Power Query searches for a specific text string in all rows of a column and replaces 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 Use special characters option.
Replace entire cell contents
Imagine a table like the following, where you have columns for Account ID, Category Name, and Sales Goal.
The value of -1 in the Sales Goal column is an error in the source and needs to be replaced with the standard sales goal defined by the business for these instances, which is 250,000. To do that, select and hold (or right-click) the -1 value, and then select Replace values. This action brings up the Replace values dialog with Value to find set to -1. Now all you need to do is enter 250000 in the Replace with box.
The outcome of that operation gives you the result that you're looking for.
Replace instances of a text string
Continuing with the previous table, let's say you want to remove the text string "Category Name: " from the Category Name column. To do that, go to the Transform group on the Home tab, and select Replace values. In the Replace values dialog box, enter the text string Category Name: (followed by a space) in the Value to find box, leave the Replace with box empty, and then select OK.
The result of that operation gives you the table in the following image.
This module introduces Power Query in Microsoft Excel, a powerful data connection, cleaning, and shaping technology that is a core part of the Microsoft modern analytics suite of business intelligence tools.