Extracting values from Kusto JSON columns in PBI

 

What is the problem

 

In Kusto (aka Azure Data Explorer aka ADX) you can have columns in a table that contain JSON structures.

In KQL it is very easy to extract elements from these columns and use them as regular columns.

It requires more resources but overall, it is standard.

An example can be found in the table TransformedMetrics in the SampleMetrics databases in the help cluster.

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('SampleMetrics')]

TransformedMetrics

| project tags

| take 1

Table0

 

 

The tags columns contains inside it host, measurement_db_type ,sql_instance,wait_type and more

 

 

If we want to use the wait_time column in a query we can use:

Execute in [Web] [Desktop] [cluster('help.kusto.windows.net').database('SampleMetrics')]

TransformedMetrics

| extend Wait_Type=tostring(tags.wait_type)

| summarize Cn=count() by Wait_Type

| where Cn> 10000 and Wait_Type !=''

| order by Cn

 

Table0

 

 

Our task is to do a similar analysis in Power BI.

One way to do that is described by Chris Web in his blog

I want to present a different approach which IMHO is simpler

 

Using Value.NativeQuery to extract from JSON

 

This M function allows us to add a snippet of native code, in our case KQL, anywhere in the query generated by the connector based on the transformation you apply to the Kusto table.

KQL is a perfect match for this approach because a query in KQL is a string of operations each taking from the previous table and creating a new table.

This is exactly the way an M query is built and so by adding a step in M, we can add a step in the generated KQL query at any position.

There are many use cases for this pattern. Here I’ll describe one use case for extracting from a JSON column

In the KQL shown above, there is one operation we want to insert into the KQL generated by PBI but currently we don’t have a builtin way to do that.

Creating the basic M query

 

I’ll assume here that you already know how to connect to Kusto from PBI and select a table from the list of tables in a database.

Don’t enter any query in the connection dialog, just specify the cluster “help” and specify if you want import or direct query.

Navigate to the SampleMetrics database and select the table TransformedMetrics

Click transform data and in the Power Query editor you’ll see

 

The tags column is shown as a record but attempting to see the content of the record will not work.

Adding a step

 

Click the Fx icon from the formula bar and a new step will be added with the formula

= TransformedMetrics1 which is just using the previous step.

Change the step from the formula bar (If you don’t see the formula bar check view/Formula Bar)

= Value.NativeQuery(TransformedMetrics1,"| extend Wait_Type=tostring(tags.wait_type)")

The function uses the previous step and in the second argument we use the exact KQL step we used in the explorer. If we need to extract more columns, we can do that in the same step

After applying the new step, the query editor will show a new column named Wait_Type

 

 

 

 

Now we can continue adding steps to aggregate and  filter it and eventually we can get the same result as in the original KQL query.

The final query from view native query in the PQ editor is

 

["TransformedMetrics"]

| extend Wait_Type=tostring(tags.wait_type)

| where strcmp(["Wait_Type"], ("")) != 0

| summarize ["Count"]=count() by ["Wait_Type"]

| where (["Count"]) > ((10000))

 

Notice that there are two where steps, one to filter out the empty wait_type and one to after the summarize to select  types with count greater than a value.

In the attached example I use a dynamic  M parameter to specify the cutoff value.

Other uses of Value.NativeQuery

 

The same mechanism can be used to use summarize options like percentiles, special where options like regex matching and many other KQL features that do not have a match in M.

What about inserting the entire query into the M script?

 

This is also a valid option, but it is more difficult to maintain.

My recommendation is in any case that you have non-trivial KQL logic, put it in a function with or without parameters, and call the function from PBI .

In cases that the Kusto connector can generate most of the KQL for you, or you are not fluid with writing KQL just use PQ steps.

If one of the features you need is not available, the Value.NativeQuery function can help in injecting the minimum amount of KQL syntax in the right place.

In any case look at the generated KQL (View native query from the last step in the M query).

You can think about it as a KQL learning tool.