A lot of us work with Power Query Editor in Power BI and are familiar with its different capabilities that it provides to a report developer. I was recently approached by one of my colleagues who has started learning Power BI, to check if there is a way to implement CONTAINS functionality on a TEXT column in Power BI in the similar fashion, we use LIKE operator in a SQL query.
Suppose I have the following SQL query which gives me count of rows from a table where the URL column in the table contains the following text values:
So, now the task was to replicate the similar query in Power BI on a dataset which is coming from a simple excel or csv file. If the data was coming from a SQL database, then writing the same query was easy within Power BI to extract the relevant data. We will take a sample dataset for this blog. The dataset is taken from the Kaggle website and the link to the dataset is https://www.kaggle.com/teseract/urldataset. I have got the screenshot of the sample data as below:
Now, in this blog we will see how we can use the list containing the strings in Power BI to achieve the above functionality by using a simple M code that will further use list functions.
In Power Query Editor, let us select the following option to create an easy list of strings that we need to match within the URL column in the data:
I just copied the LIKE part of the above SQL query and entered it into a table as follows:
Once we click OK, we end up with the following table:
Now we need to clean this “LikeString” column, so we end up with only the string values. (This step is considered as in some scenarios we can end up with many more string values in a SQL query which may be cumbersome to type-in as values in the ENTER DATA window). I just use EXTRACT feature in Power Query Editor to extract the string between delimiters.
Once we do this, we end up with the cleaned text values in the “LikeString” column. (We have considered just 6 text strings for the matching criteria; we can have more than these as well)
The next task for us is to convert this string table to a list:
Once you do that, the icon against our Matching String table changes to that of a list item:
Now the real magic is adding a single line of code under Advanced Editor for the URL dataset as shown below: