Dynamic text search in Power Query

[Migasuke]

Use case description

Imagine we own several stores in one big shopping mall. We noticed that people write their reviews about all stores on the website of the shopping mall. What does it mean? It means we have hundreds or even thousands of reviews and comments in one single page but majority of those comments are irrelevant to us since we own only few shops in the mall and not every single one of them.

Main idea of our solution has following steps:

     1. We provide list of our stores.

     2. We search if those stores are mentioned in any of the comment.
     3. We keep only those comments which are related to our stores.

Of course, everything should be automatic and possible to run in PBI Service.

Initial Set up

To keep the example simple I will work with only two queries:

1. First query is called “Comments” – this is the query which contains all the comments from the Shopping mall. Example is following:

2. The next query is related to our companies called Companies. It’s basically a list of the text strings we want to find:

So basically we search for three companies called Super Buyyy, Order Anything 27/7 and Massive Discounts ABC.

Solution

1. Let’s start with a very first step. If you have a table similar to my Companies table, be sure you change the table into the list. In case you don’t know how to do it, we can simply do right click on the column header and select Drill Down:

You should notice that the icon of the query has also changed:

Changing the table to a list is a crucial step because then we can take advantage of List functions in Power Query.


2. In the next step we will go to the Comments table and add a new custom column. Syntax for the query is very simple and we basically refer to our created Companies list:

Result should look like this:

3. The same way as we did before we will create a custom column, which contains find logic. This time the code is a bit more tricky:

Now let’s look more closely into the formula.
List.Contains is a function which validates if some of our values from Companies List can be found within Comments column and returns TRUE or FALSE. Basic formula would work only if we had 100% match of the value from List with our Comments. Example can be seen below:

Since we always compare Comments, which are different from our initial list values, we need to adjust the code by the equation criteria which would be:

(x as text, y as text)=>Text.Contains(Text.Lower(y),Text.Lower(x))

What does the formula above mean? Basically:

• We define variables x and y as a text.
• x is represented as our Companies List.
• y is represented as our Comments.
• Text.Lower ensures that we work only with lower case text (Super Buyyy vs super buyyy is not the same in terms of comparison)
• Text.Contains compares our Comments with the List values.

Result of our new column should look like this:

In the final step we can just remove column with Lists and apply filter on values equal to TRUE so we exclude companies we are not interested in: