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:
Replacing Values in the Source Query in Excel
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.
Replacing Values in the Source Query in Excel
Step 2: Go to the source query interface by going to the Data tab, selecting From Table/Range.
Replacing Values in the Source Query in Excel
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.
Replacing Values in the Source Query in Excel
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.
Replacing Values in the Source Query in Excel
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-“.
Replacing Values in the Source Query in Excel
And click OK to complete.
Replacing Values in the Source Query in Excel
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.
Replacing Values in the Source Query in Excel
However, after converting the data, you may encounter an Error row.
Replacing Values in the Source Query in Excel
To fix this error, you will do the following: Go to the Transform tab, select Replace error.
Replacing Values in the Source Query in Excel
Display the Replace Errors table, where you will enter the value to replace when an error occurs.
Replacing Values in the Source Query in Excel
And click OK, the error will be immediately replaced with another value.
Replacing Values in the Source Query in Excel
Step 8: To export the data to Excel, go to the Home tab, select Close & Load.
Replacing Values in the Source Query in Excel
The exported data will be displayed in a new sheet.
Replacing Values in the Source Query in Excel
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!
