Power Query Problems

How to Use IF AND with Text.Contains in Power Query

Camila Milosovich Oct 14, 2026 870 views

Question details

The user needs to return a conditional replacement value in Power Query when a heading exactly matches a specific word and a description contains a target word within a larger sentence.

How to Use IF AND with Text.Contains in Power Query

Product: Power Query
Device & OS: not provided
Scenario: Cleaning and standardizing data where values must be replaced based on multiple text conditions, including partial matches inside strings with irregular casing and spacing.
Observed behavior: A standard Power Query IF formula fails or misses matches due to case sensitivity and leading/trailing spaces in the raw text.

Before you start

Ensure your dataset is loaded into the Power Query Editor and take note of the exact column names for your heading and description data to avoid syntax errors.

Solution 1Recommended

Use a Custom Column with IF, AND, and Case-Insensitive Text.Contains

Create an M formula that ignores text casing and trims extra spaces to accurately evaluate multiple conditions.

Power Query (M) is case-sensitive by default. To accurately find a word like 'Ladies' inside a messy text string, you must use the 'Comparer.OrdinalIgnoreCase' parameter. Additionally, standardizing the heading text using 'Text.Lower' and 'Text.Trim' prevents extra spaces from breaking your exact match condition.

Add a Custom Column

In the Power Query Editor, go to the 'Add Column' tab on the ribbon and click on 'Custom Column'.

Write the M Formula

In the Custom Column dialog, enter the following formula: = if Text.Contains([Description.1.1.1], "Ladies", Comparer.OrdinalIgnoreCase) and Text.Lower(Text.Trim([Heading])) = "footwear" then "Ladies Safety Footwear" else [Sub Heading]

Apply and Verify

Click 'OK' to create the column. Review the new column in the data preview to ensure the correct values have been replaced, then click 'Close & Load' to apply the changes to your worksheet.

Use a Custom Column with IF, AND, and Case-Insensitive Text.Contains

Case Sensitivity Managed: By adding Comparer.OrdinalIgnoreCase, the formula will successfully match 'ladies', 'Ladies', or 'LADIES' regardless of how it was originally typed in the dataset.

Free Microsoft Office alternative

Edit and Process Data Easily with WPS Office

Looking for a fast, lightweight, and highly compatible tool for managing your spreadsheets? WPS Office offers robust data analysis tools, advanced formula support, and familiar spreadsheet functions without the heavy subscription fees.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your device.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and seamlessly open your existing .xlsx or .csv data files.
3. Process Your Data: Utilize built-in text functions, conditional formatting, and filtering tools to effortlessly clean and organize your datasets.

Free and lightweight alternative to Microsoft ExcelHighly compatible with Microsoft Office formats (.xlsx, .xls, and .csv)Intuitive user interface for fast data cleaning and conditional formattingSeamless migration with no learning curve for existing Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why is my Text.Contains formula not finding words?

By default, Power Query M is case-sensitive. If your text contains variations in capitalization, the standard function will fail to find a match. Add 'Comparer.OrdinalIgnoreCase' as the third argument in your Text.Contains function to fix this.

How do I ignore trailing spaces in Power Query conditions?

Wrap your column reference in the Text.Trim() function. For example, using Text.Trim([Heading]) will remove leading and trailing spaces before evaluating your logical condition.

Can I use multiple conditions in an IF statement in Power Query?

Yes, you can combine multiple logical tests using the 'and' or 'or' operators. When using 'and', both conditions must evaluate to true to trigger the 'then' result.

What happens if the IF condition is not met in Power Query?

Unlike Excel where you can sometimes omit the false condition, Power Query requires an 'else' clause. If the conditions are not met, the formula returns the value specified after 'else', which can be a static value, null, or a reference to another column.

Excel & Data Problems