Use Multiple IF and OR Conditions in an Excel Power Query Custom Column
Question details
Create a Power Query custom column that checks whether a text column contains any of several specified strings by using multiple IF conditions with OR logic.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user needs to classify or return a value when a source column contains TextA, TextB, or another specified text value.
- Observed behavior
- The desired result is for the custom column to return a chosen result when any text condition is true and return null when no conditions match.
Confirm the exact source column name in Power Query and make sure its data type is Text. Decide which text values should trigger the result and what the custom column should return.
Create a Custom Column with IF and OR Text Conditions
Use this method when a Conditional Column cannot express all of your combined text checks.
A Custom Column accepts Power Query M formulas, so you can combine several Text.Contains checks with the or operator. Replace Column1, TextA, TextB, and Result with your own column name and values.
In the Power Query Editor, select the Add Column tab and choose Custom Column.
Enter a formula such as: if [Column1] <> null and (Text.Contains([Column1], "TextA") or Text.Contains([Column1], "TextB")) then "Result" else null.
Enter a name for the new column, then select OK. Review the preview to confirm that rows containing either text value return Result.
Select Home > Close & Load in Excel when the custom column returns the expected values.
Match Text Without Case Sensitivity in a Power Query Custom Column
Use this variation when values such as texta, TEXTA, and TextA should all be treated as matches.
Use WPS Office for Everyday Excel Workbook Editing
Power Query M formulas are managed within Microsoft Excel Power Query. For everyday spreadsheet editing and a smooth transition from Excel files, WPS Office is a free, lightweight alternative with a familiar interface and strong workbook format compatibility.
Frequently Asked Questions
Why should I use a Custom Column instead of a Conditional Column in Power Query?
Use Add Column > Custom Column when you need compound M logic, such as multiple Text.Contains tests joined with or. Conditional Column is better for simpler rule-by-rule comparisons.
How do I test for three or more text values in a Power Query custom column?
Add another Text.Contains expression inside the parentheses, joined with or. For example: Text.Contains([Column1], "TextA") or Text.Contains([Column1], "TextB") or Text.Contains([Column1], "TextC").
How can I prevent errors when the Power Query column contains null values?
Check for null before Text.Contains, such as: if [Column1] <> null and (Text.Contains([Column1], "TextA") or Text.Contains([Column1], "TextB")) then "Result" else null.
Can Power Query match text regardless of uppercase and lowercase letters?
Yes. Add Comparer.OrdinalIgnoreCase to each Text.Contains function, for example Text.Contains([Column1], "TextA", Comparer.OrdinalIgnoreCase).
Where can I edit an existing custom column formula in Power Query?
In the Query Settings pane, find the Added Custom step under Applied Steps and select its gear icon. You can then revise the formula in the Custom Column dialog.
