Power Query If Text Contains: Simplifying Conditional Data Filtering

I’ve found that Power Query really shakes up the experience we have with Excel and Power BI Desktop, especially when it comes to dealing with strings. It’s pretty cool how the Text.Contains function allows us to check for specific substrings within a larger string. It’s like having a fine-tuned search engine to sift through our data.

Now imagine you have a list of names, each tagged with a specific suffix to indicate gender—kind of an old-school practice, but it’s what we’ve got. Using Power Query, I can easily spot which names have the female suffixes without breaking a sweat. And the IF statement, that’s another handy tool in our Power Query arsenal—it lets you perform these checks within certain conditions, making our data manipulation even more flexible.

Using TEXT.CONTAINS in Power Query

When I’m looking to filter data based on specific text criteria, TEXT.CONTAINS is my go-to function in Power Query. Here’s a straightforward way to apply it:

1. After pulling up data, I navigate to Data Tab > Get Data > From Other Sources > From Table/Range.

   

2. Then I add a custom formula by going to Add Column > Custom Column.

   

3. In the formula dialog box, I enter =Text.Contains([Names],"Ms.").

   

4. I finish by clicking OK, which adds a column indicating True where “Ms.” is found, and False otherwise.

   

TEXT.CONTAINS isn’t alone; other text functions like TEXT.START, TEXT.END, and TEXT.SPLIT help me manipulate and assess text data seamlessly. These functions, along with the logical list.contains and date functions, enhance my ability to merge data and analyze it effectively.

How this Function Works

## How this Function Works

In Power Query, **Text.Contains** is an operator I use to detect if a string includes a certain substring. Here's the syntax:

Text.Contains(text as nullable text, substring as text, optional comparer as nullable function) as nullable logical

- **String**: The text in which I'm searching.
- **Substring**: The sequence of characters I'm looking for within the string.
- **Comparer** (Optional): Adjusts the search for **case-sensitive** or **culture-aware comparison**.

When matching:
- **True** if the substring exists within the string.
- **False** if absent.

Remember, this function isn't friendly with wildcards or regular expressions. For culture-specific searches, I apply a comparer like `Comparer.FromCulture` to handle varying linguistic rules.

If the syntax isn't precise or I mess up the case sensitivity, I'll be greeted with an error message.

![how-text-contains-function-work](https://cdn-amgoo.nitrocdn.com/qJvQlgGQEOwNXyhUqNwiAWOQgCDvoMdJ/assets/images/optimized/rev-4318c60/excelchamps.com/wp-content/uploads/2023/05/6-how-text-contains-function-work.png)

Use IF to Check Text Contains

When I’m fiddling with data in Power Query, especially when I want meaningful labels instead of plain old TRUE or FALSE, I often resort to the trusty IF statement. It’s sort of like a Swiss Army knife for conditional logic in my data transformations. Let me share a quick way to use it:

Here’s a use case I run into often. Imagine I’ve got a list of names, and I need to label them by gender based on a prefix like “Ms.” So, I’d pop over to Add Column, hit Conditional Column, and then lay down some rules:

• Column Name: I’d typically name it something helpful, like “Gender”.
• Column to evaluate: I’d select the specific column with the names.
• Operator: Set this to “contains”.
• Value: This would be “Ms.” since that’s my clue.
• Outputs: I’d enter “Female” for a match, and “Male” for no match.

Once I’ve got this all lined up, I click OK. Just like that, every row in my table now sports a brand-new column with “Female” or “Male” neatly stamped based on whether the text contains “Ms.” or not.

Here’s what I especially dig about this approach:

• It’s clean: No messy nested IFs or complicated logic.
• It’s flexible: Swap out “Ms.” with any string or condition.
• It respects the case: By default, “contains” is case-sensitive, but if I need to ignore case, I might throw in Comparer.OrdinalIgnoreCase as an argument.

So, whether it’s sorting names, flagging specific phrases, or categorizing rows, a conditional column with IF is my go-to solution. It’s simple, it’s elegant, and it gets the job done.

Related Tutorials

Here are some handy guides I’ve found useful:

• Merge Excel Files: Got a bunch of Excel files? Combine them into one workbook.

• VLOOKUP: Hunting for data across tables? Here’s how to perform VLOOKUP in Power Query.

• Unpivot Data: Make your data analysis-ready by learning how to unpivot data in Excel.

• Concatenate Values: Whether it’s text or numbers, mastering concatenation in Power Query is a game-changer.

• Date Difference: Need to calculate the days between dates? Check out Power Query’s Date Difference magic.

• Functions and Formulas: Get to grips with the nuts and bolts of Power Query formulas.

• IF Statement: Decision-making in your data? Apply the IF Statement in Power Query.

For more, don’t forget the full Excel Power Query Tutorial for extra insights and to get data bending to your will with Power Automate.