Power Query: IF Text Contains

puneet-gogia-excel-champs

- Written by Puneet

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.

text-contains-function-in-power-query

Using TEXT.CONTAINS in Power Query

  1. Go to the Data Tab > Get Data > From Other Sources > From Table/Range.
    get-data-from-table-range
  2. Now, go to the Add Column > Custom Column in the power query editor.
    custom-column-in-power-query-editor
  3. From here in the custom formula dialog box, enter (=Text.Contains([Names],”Ms.”)).
    in-dialog-box-enter-text-contains-function
  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.

new-column-added-with-true-or-false

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.

how-text-contains-function-work

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.

if-to-check-text-contains

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

conditional-column-to-create-if

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”.

new-column-after-if-statement

Get the Excel File

Download
Last Updated: December 25, 2023