How to Replace a Power Query Value Based on Two Conditions
Question details
The user needs to replace a value in a specific column only when two exact conditions are met across two different columns.
- Product
- Power Query (Excel)
- Device & OS
- not provided
- Scenario
- Editing data transformation steps in Power Query where an initial not-equal condition inadvertently affected incorrect rows.
- Observed behavior
- The replacement logic affected the wrong rows. The goal is to accurately target and replace values such as 'D26 Plumbing' with 'D26 Plumbing & Bath' only when the 'SiteCollection' is 'en_ca'.
Ensure you have your data loaded into the Power Query Editor and that the column names in your M expression exactly match the headers in your dataset.
Use Power Query M Expression with Equality Tests
Apply a custom Table.ReplaceValue formula with an 'if...and...then' statement to ensure values are only replaced when both conditions are strictly met.
Using 'not-equal' logic in conditional replacements can often yield unexpected results because it applies broadly to anything outside the condition. Defining exact equality tests prevents the replacement logic from affecting the wrong rows.
Select your data table in Excel, go to the 'Data' tab, and click 'From Table/Range' to open the Power Query Editor.
Click the 'fx' icon next to the formula bar to insert a new step, or go to the 'Advanced Editor' on the Home tab to modify your existing script.
Input the following formula: =Table.ReplaceValue(#"Filtered Rows", each [Subsite], each if [SiteCollection] = "en_ca" and [Subsite] = "D26 Plumbing" then "D26 Plumbing & Bath" else [Subsite], Replacer.ReplaceValue, {"Subsite"})
Press 'Enter' to apply the formula. Check the preview grid to ensure only the targeted rows in the 'Subsite' column have updated. Click 'Close & Load' to return your transformed data to the spreadsheet.
Try WPS Office for Seamless Data Management
While Power Query is a specific Microsoft Excel feature, WPS Office provides a highly compatible, free, and lightweight alternative for everyday spreadsheet tasks, complex data filtering, and analysis.
Frequently Asked Questions
Why did my original not-equal condition replace the wrong rows in Power Query?
A not-equal condition (like <> 'en_ca') applies to every single row that doesn't match, which often includes rows you didn't intend to target. Using exact equality tests (if A = 'x' and B = 'y') ensures only specific rows are modified.
Can I add a third condition to my Power Query replace value formula?
Yes. You can expand the 'if' statement by adding another 'and' operator. For example: if [Column1] = "A" and [Column2] = "B" and [Column3] = "C" then...
How do I conditionally replace multiple different values in the same column?
You can chain multiple conditions using 'else if'. For example: if [Subsite] = "D26 Plumbing" then "Bath" else if [Subsite] = "D27 Electrical" then "Wiring" else [Subsite].
