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.

  1. 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.

  2. Select a column with a text data type.

  3. Select Home or Transform  > Replace Value. The Replace Values dialog box appears.

  4. In the Value To Find box, enter the value to search.

  5. In the Replace With box, enter the value to replace.

  6. 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 FeedCarriage Return and Line Feed, and Non-breaking Space.

  7. 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.

  1. 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.

  2. Select a column with a number, date/time, or logical data type.

  3. Select Home or Transform > Replace Value. The Replace Values dialog box appears.

  4. In the Value To Find box, enter the value to search.

  5. In the Replace With box, enter the value to replace.

  6. Select OK.

See Also

Power Query for Excel Help

Add or change data types

Replace values and errors (docs.com)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×