Replace values (Power Query) - Microsoft Support (2023)

Table of Contents
See Also Need more help? Videos

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 More...Less

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 canbe 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. TheReplace Values dialog box appears.

  4. In the Value To Findbox, enter the value to search.

  5. In theReplace Withbox, enter the value to replace.

  6. Under Advanced options, do one or more of the following:

    Match Bydefault, specific text values are replaced. To replace the the whole cell value, selectMatch entire cell contents.

    (Video) Replace Values Based on Condition (in a Single Step) in Power Query

    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.

  7. SelectOK.

(Video) Power Query Replace Values the safer way

The default behavior for non-text columns is to search and replace the full contents of a cell. This behavior can’t be changed.

(Video) Tips For Using Replace Values In Power Query

  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. TheReplace Values dialog box appears.

  4. In the Value To Findbox, enter the value to search.

  5. In theReplace Withbox, enter the value to replace.

  6. SelectOK.

See Also

Power Query for Excel Help

(Video) Replace multiple values in Power Query in one step

Add or change data types

Replace values and errors (docs.com)

Videos

1. Power Query for Excel - Replace values using values from another column
(The Power User)
2. Microsoft Excel and Power Query - Video 11 Replace Values
(Software Solutions)
3. Replace text with another column value in Power Query
(Curbal)
4. Replace Values and Column Data Types with Power BI (Power Query)
(The Power User)
5. Replace Errors in power query with specific values power BI
(Learn 2 Excel)
6. Replace Error Values in Multiple Columns Power Query
(Goodly)
Top Articles
Latest Posts
Article information

Author: Cheryll Lueilwitz

Last Updated: 12/31/2022

Views: 5776

Rating: 4.3 / 5 (74 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Cheryll Lueilwitz

Birthday: 1997-12-23

Address: 4653 O'Kon Hill, Lake Juanstad, AR 65469

Phone: +494124489301

Job: Marketing Representative

Hobby: Reading, Ice skating, Foraging, BASE jumping, Hiking, Skateboarding, Kayaking

Introduction: My name is Cheryll Lueilwitz, I am a sparkling, clean, super, lucky, joyous, outstanding, lucky person who loves writing and wants to share my knowledge and understanding with you.