If you are an Excel user, then at some point you have probably come across the need to search for multiple keywords in one column. Maybe you have a list of products and you want to know which ones contain the word “red” or “green”. Or maybe you have a list of customer names and you want to know which ones contain the letters “aeiou”. Whatever the case may be, if you need to search for multiple keywords in one column, then there is a very easy way to do it using Power Query.
Power Query is a free add-in from Microsoft that allows you to easily connect to data sources, transform and shape that data, and then load it into Excel or Power BI. One of the many great features of Power Query is its ability to search for text patterns using regular expressions (regex). Regular expressions are a powerful way of pattern matching that can be used for all sorts of different things, including searching for multiple keywords in one column.
Let’s say we have the following table of data:
Name Color Product
John Red Shirt
Mary Green Pants
Mike Blue Shirt
Steve Yellow Hat
And we want to know which products contain the colors “red” or “green”. We could do this with a simple COUNTIF function in Excel:
=COUNTIF(B2:B5,”*red*”)+COUNTIF(B2:B5,”*green*”)
But what if we had a lot more colors that we wanted to search for? Or what if the colors were in different columns? COUNTIF starts to get unwieldy when searching for multiple keywords in one column. That’s where Power Query comes in.
To use Power Query to search for multiple keywords in one column, we first need to add a custom column. To do this, select the “Name” column and then go to the “Add Column” tab on the ribbon and select “Custom Column”:
After selecting “Custom Column”, a new window will open up where we can enter our custom column formula:
Our formula is going to use the Text.Contains function which searches a text value for another text value and returns true or false depending on whether or not it finds a match. We are going to use Text.Contains to search our “Name” column for each color that we are interested in. Our formula will look like this:
=Text.Contains([Name],”red”) || Text.Contains([Name],”green”) || Text.Contains([Name],”blue”) || Text.Contains([Name],”yellow”)
Let’s break this down so that it is easier to understand what is going on:
[Name] – This is our column name that we are going to search through (i.e., B2:B5).
red – This is the first color that we are searching for (i.e., C2).
green – This is the second color that we are searching for (i .e., D2).
blue – This is the third color that we are searching for (i .e., E2).
yellow – This is the fourth color that we are searching for (i .e., F2).
|| – This is an OR operator which means that if either condition1 OR condition 2 is true, then return true. In our case, condition1 is Text.Contains([Name],”red”) and condition2 is Text . Contains([Name],”green”). So if our Name column contains either “red” or “green”, then this formula will return true . And finally, let’s take a look at what our resulting table looks like:
As you can see from our results table, our custom column has correctly identified which products contain the colors “red” or “green” (or both). In just a few quick steps, we were able to use Power Query Search for Multiple Keywords feature 7in order 8to easily search through onecolumn9for multiple10keywords11and get 12the results13that 14we 15were looking16for17!
