There is a cool feature of Power Query which is known as ‘Replace Values’ feature. As the name suggests, it can replace any value with the new value you provide into your data table.
Power Query Helps In Replacing Values
To understand this feature in detail, let’s take an example.
We have a data set as shown in the steps below. We need to change the Country of a person as he has shifted from India to U.S. Instead of changing the entire data table, we can directly make changes in the Power Query as explained in the steps below.
Step 1: Select the data for replacing
Select the columns of the values you want to replace. Here, we have selected the Country column as we want to replace the value ‘India’ as shown in the picture below.
Step 2: Select Replace Values option
To replace values in Power Query go to –
Transform > Replace Values button > Replace Values option
Step 3: Insert New Value
After you click on Replace Values option, a window gets open where you can replace the value that you don’t want to appear in the data table as shown below.
Step 4: Close & Apply
When you click on OK in the Replace Value window, it will change the value ‘India’ to ‘U.S’ as shown in below picture. To apply all the changes just click on the Close & Apply button.
You can acquire more knowledge :
- Split First & Last Name
- Consolidate Multiple Excel Workbooks
- Power Query Helps in Transposing Data
- Learn How to do Reverse Rows Using Power Query