If you need to find if text is in a column you can use the Text.PositionOf function to confirm it exists.

In the table below we want to identify the rows that have a red product. I don’t want this to be a case sensitive search. So we will find RED, red and Red.

We can use the From Table/Range icon on the Data ribbon and to import the table into Power Query.

Click the Add Column ribbon tab and click the Add Column icon.

Change the name and then enter the following formula.

=Text.PositionOf(Text.Lower([Product]),"red")>=0

Note the formula entry is case sensitive. Click OK.

The Text.PositionOf will return -1 if the entry cannot be found, hence the test for >= 0.

The Text.Lower function changes the column entry to lowercase. So you need to use lower case characters between the quotation marks.

The output looks like this.

If you want to match the case exactly in the search use the following formula.

=Text.PositionOf([Product],"Red")>=0

It is not perfect and the downside is that it will also return TRUE for Fred Flinstone as a product because the text red is in Fred.

If your text to find is unique enough, it will be effective.