Power Query: IF Text Contains

In Power Query, you can use the Text.Contains a function that you can use to test whether a sub-string is in the string or not. Apart from this function, we can also use the IF statement to test for a substring.

In the below example, we have a list of names with the suffix for male and female. And we need to check which are the names with the female suffix.

Using TEXT.CONTAINS in Power Query

1. Go to the Data Tab > Get Data > From Other Sources > From Table/Range.
   
2. Now, go to the Add Column > Custom Column in the power query editor.
   
3. From here in the custom formula dialog box, enter (=Text.Contains([Names],”Ms.”)).
   
4. In the end, click OK to add the columns.

When you click OK, it will enter a new column with TRUE and FALSE. TRUE where Text contains “Ms.” and FALSE for else.

How this Function Works

In the function, you have two arguments to define. In the first argument (Text), the column with the main Text from which you want to check the condition.

And in the second argument (substring), you need to specify the substring which you want to check in the main string of the first argument.

Use IF to Check Text Contains

You can also use the IF statement to check if a text contains another text. By using IF, you can use meaningful results instead of TRUE and FALSE. So, in the power query editor, you need to go to the Add Column and click the Conditional Column.

When you click on the Conditional Column, it will show a dialog box to create an IF statement.

Here you need to:

1. Enter the Column Name.
2. Select the column name to check the condition.
3. Select the operator “contains”.
4. Value to check, “Ms.”.
5. Output to get if the condition is TRUE, “Female”.
6. And output to get is condition is FALSE, “Male”.
7. In the end, click OK to enter the column.

The moment you click OK, you will get a new column which has “Female” where Text contains “Ms.” is true, and for else for “Male”.