Search text in a Kusto/ADX table using any number of terms from Power BI

[Guest DanyHoter]

Search ADX text by multiple terms from PBI

 

 

 

Summary

 

 

 

 

In this article, I’ll describe how we can search an ADX/Kusto table using multiple words provided by the user.

 

Step by step

 

 

 

 

Connect to the table StormEvents in the database Samples on the ADX cluster help.

 

Make sure to use direct query.

 

 

 

 

 

 

 

 

 

 

In the Power Query editor add a step to the M script

 

 

 

 

 

 

 

 

The new step applies a filter to the column EventNarrative using the operator has_all.

 

This operator returns all rows that contain a list of terms in a text regardless of case.

 

If we wanted to find text that include at least one term from a list, we can use the operator has_any.

 

For the user to provide any number of words we take the string provided by the user and break it by the delimiter ~ using the split function.

 

The user can enter a search text like “one~two~three” to look for rows where the searched column includes “One” and “two” and “Three” as terms surrounded by special characters.

 

If the list of terms is known ahead of time, we could create a table of terms and allow selecting multiple from the table. In our case we assume any term can appear.

 

 

 

 

 

 

 

 

 

 

= Value.NativeQuery( #"Removed Other Columns","| where EventNarrative has_all (split('snow~rain','~'))")

 

 

 

The M function Value.NativeQuery allow us to insert a KQL snippet anywhere in the generated query.

 

In our case the full query will be:

 

["StormEvents"]

 

| project ["StartTime"],["State"],["EventType"],["EventNarrative"]

 

| where EventNarrative has_all (split( "cold~snow",'~'))

 

 

 

Now we need to create a parameter that will replace the literal used for filtering.

 

 

 

 

 

 

 

 

 

 

 

 

Once we create the parameter, we replace the literal with the parameter.

 

A better method will be to use a function that will get the list of words or one string with multiple delimited words.

 

 

 

 

 

 

 

 

After we apply the query, we need to create an empty table so we can bind the parameter to a column.

 

There are no rows in the table because we ae not selecting from a known list.

 

In the model view, we can bind the column in this table to the parameter.

 

 

 

 

 

 

 

 

In the page we create a page level filter and use the is type of filter.

 

This allows us to input arbitrary values.

 

 

 

 

 

 

We add to the page a table with the columns from the storms query and a measure that shows the numbers of rows that are returned by the filter.

 

 

 

 

Continue reading...