Home ยป Functions

How to Replace Values in Excel Power Query

The Power Query Excel feature is one of the amazing Excel features. Using this feature, you can extract data from current excel workbook tables and/or from other external sources into Excel. Additionally, you can play around with the extracted data by applying transformation on this data as per your need. You can find more detail on the basics of power query in our overview blog. On this page, we would learn how to replace values using this amazing power query feature of excel.

So let us begin with unlocking the technique of replacing values in excel using power query. ๐Ÿ˜‰

Are you using Excel 2010 or 2013 version? If yes, then you need to download and install the power query add-in to your excel. It is not available by default. Check this link.

Sample Data – Replace Values in Excel Power Query

The below image shows the information about units sold by sales officers in their respective regions during December 2020.

Sample Data - Replace Values in Excel Power Query

Now, we want to replace the text ‘MATXXX’ from Material Code with a new text ‘Material-XXX’.

To use the excel power query feature on the datasets, it is important to make sure that the dataset is in an excel table format. The power query feature does not work on normal tabular data. You need to convert the normal tabular data into an excel table format. To do so, select the data cell range. Press Ctrl + T and click the button ‘OK’ in the new dialog box ‘Create Table’.

Now, once the normal dataset is converted into an excel table format, we are good to start with learning how to replace the text ‘MATXXX’ with a new text ‘Material-XXX’ in this table.

2 Step Procedure to Replace Values in Power Query

There are two basic steps to replace values in excel power query tool.

  • Firstly, to extract the table into Power Query Editor window
  • And then, proceed with replacing values.

Let us go further with each of these two steps one by one.

Extract or Import Table Data into Power Query Editor Window

As learned above, in order to work with datasets in power query, it is important that the dataset is in proper excel format, and not just a normal table.

To import the excel table data into power query window, firstly click anywhere on the table. Then, navigate to the path – Data tab > Get & Transform Data group > Get Data > From Other Sources > From Table/Range.

Get Data From Table Range Navigation

This would open up the ‘Power Query Editor’ window with its default name as the excel table name. You would notice that your table appears in this window.

Power Query Editor Window with Table Data

Once the excel table is visible in the power query editor window, let us now unlock the technique to replace the text ‘MATXXX’ with a new text ‘Material-XXX’.

Replacing Values in Power Query Editor Window

Follow the undermentioned steps to replace the text with new text in the power query editor window.

  • Click on the header of the column – ‘Material Code’ to select the header column.
  • Now, go to the ‘Transform’ tab, and select the ‘Replace Values’ option under ‘Any Column’ group, as highlighted below:
Transform Tab Replace Values Power Query
  • In the ‘Replace Values’ dialog box that appears, enter the ‘Value to Find’ and ‘Replace With’ input boxes and click OK. See the image below.
Replace Text with Another Text Power Query

As a result, you would notice that excel replaces the text ‘MAT’ with the new text ‘Material’. Check the ‘Applied Steps’ section. The new step gets added at the end, as shown in the image below.

Replaced Value Applied Step Power Query

Finally, to load the new data from the Power Query Editor window back to the excel workbook, simply go to the ‘Home’ tab and choose the option ‘Close & Load’, as shown below:

Power Query Editor - Close & Load Icon

As a result, excel would load the new table with replaced values to a new worksheet in the existing workbook.

How to Replace Values in Excel Power Query

Question That May Arise In Your Mind

You might be wondering, why are we using the power query tool to replace values when we can do it directly in the original table itself by using the ‘Excel Find and Replace‘ dialog box.

There is a simple answer to this.

The Power Query Editor window is not just used to extract and transform the dataset, but also to store the transformation steps and create connections for the applied steps.

While replacing values in power query editor window, the excel stored what we did, like this.

Replaced Value Applied Step Power Query

Now, suppose there is some change in the source excel table or there are new entries added below the source table.

To quickly get the transformed version of this new data to excel, the stored connection would be helpful. There is no need of performing the above-learned steps again.

Let us test this by making some changes to the source table.

For illustration purpose, I have added some new rows of data to the source table, like the way shown in the image below. Material Code name kept as ‘MATTestX’.

New Rows of Data Added to Source Table

Now, to get the transformed versions of these new rows of data, simply go to the worksheet containing the new table (that we just created using the power query). Right-click anywhere on the new table and then choose the option ‘Refresh‘.

Right Click and Refresh Power Query

On refresh, excel performs the saved steps on the new updated source table and extract the latest data with replaced values. There was no need of performing the above-learned steps again and again.

Result of Replaced Values Refresh

With this, we have completed this tutorial on How to replace text with other text using power query excel feature.

Leave a Comment