In Power Query, you can replace one value with another value in a selected column. You can replace specific values or the whole value in a cell. Replacing values in a query does not edit the external data source in any way.
The default behavior for text values is to search and replace a specific text string. This behavior can be changed.
-
To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
-
Select a column with a text data type.
-
Select Home or Transform > Replace Value. The Replace Values dialog box appears.
-
In the Value To Find box, enter the value to search.
-
In the Replace With box, enter the value to replace.
-
Under Advanced options, do one or more of the following:Match By default, specific text values are replaced. To replace the the whole cell value, select Match entire cell contents.
Special characters To handle special characters, select Insert special characters and then select one of the following from the drop-down list: Tab, Carriage Return, Line Feed, Carriage Return and Line Feed, and Non-breaking Space.
-
Select OK.
The default behavior for non-text columns is to search and replace the full contents of a cell. This behavior can’t be changed.
-
To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.
-
Select a column with a number, date/time, or logical data type.
-
Select Home or Transform > Replace Value. The Replace Values dialog box appears.
-
In the Value To Find box, enter the value to search.
-
In the Replace With box, enter the value to replace.
-
Select OK.
