Leveraging Benefit of Power Query Editor List Functions in Power BI

[Pragati11]

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:

SELECT Count(*) AS totalRows
FROM   mytable
WHERE  pageurl LIKE '%movie%'
        OR pageurl LIKE '%owners%'
        OR pageurl LIKE '%spyware%'
        OR pageurl LIKE '%game%'
        OR pageurl LIKE '%lucky%'
        OR pageurl LIKE '%computer%'; 

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:

#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each List.AnyTrue(List.Transform(MatchingString, (substring) => Text.Contains([url], substring))))

Once you click Done, the web locations under the URL column only show the links that have the text values that we declared in the list.

Let us have a closer look what this last line of filtering rows is doing in this advanced editor window:

The details on using the above List functions can be found on the official Microsoft’s website. The links for them are as follows:

• Text.Contains - https://docs.microsoft.com/en-us/powerquery-m/text-contains
• List.Transform - https://docs.microsoft.com/en-us/powerquery-m/list-transform
• List.AnyTrue - https://docs.microsoft.com/en-us/powerquery-m/list-anytrue

So, this is a simple way of using the list capabilities within Power BI’s Power Query Editor using M Functions.

Pragati