Replacing Values in the Source Query in Excel
When processing data, you may want to replace values displayed in a certain format, but do not know how to do it. Don’t worry, in this article, I will guide you on how to replace values in the source query in Excel. Let’s take a look below!
Replacing values in Power Query
I have data as below:
The topic requires changing the values in the Product column to numeric category.
To do this, you will perform the following steps:
Step 1: First, your data must be converted to a table format. To create data in table format, select any cell in the data range, then go to the Insert tab, select Table, or press Ctrl + T and click OK.
Step 2: Go to the source query interface by going to the Data tab, selecting From Table/Range.
Display the Power Query Editor interface. Here, you will identify the object to be processed, which in this case is changing the value in the Product column to a numeric category. Therefore, you will select the Product column by clicking on the column header.
Step 3: On the toolbar, go to the Transform tab, select Replace Values.
Step 4: Display the Replace Values table. In the Value To Find section, enter the content you want to replace, in the Replace With section, enter the content to be replaced with. In the example above, because I want to remove the “SP” character and keep only the number, I will enter a space in the Replace With section.
Step 5: Click OK to complete.
Note: If you want to display “SP-01” to differentiate the product code behind, then in the Value To Find section, enter “SP”, and in Replace With, enter “SP-“.
And click OK to complete.
Step 7: To convert the Product column data to a number format, you will select the Product column by clicking on the column header, then go to the Transform tab and select Data type: Whole Number.
However, after converting the data, you may encounter an Error row.
To fix this error, you will do the following: Go to the Transform tab, select Replace error.
Display the Replace Errors table, where you will enter the value to replace when an error occurs.
And click OK, the error will be immediately replaced with another value.
Step 8: To export the data to Excel, go to the Home tab, select Close & Load.
The exported data will be displayed in a new sheet.
So I have finished guiding you on how to replace values in the source query in Excel. I hope this article will be helpful to you. If you find this article useful, don’t forget to rate it below to help me out!