How to convert a KQL query result to simple text or a table?

Like (0) Share

Report

Posted on 4 Apr 2024 14:20:54 by Shadout7 Microsoft Employee

Hi folks

I'm using a KQL query connector action in a topic, receiving a record type output.

{
 "value": [
 {
 "Count": 121,
 "IsAtRisk": 0,
 "IsFailed": 0,
 "IsSev2": 0,
 "Status": "ACTIVE"
 },
 {
 "Count": 22,
 "IsAtRisk": 1,
 "IsFailed": 0,
 "IsSev2": 0,
 "Status": "ACTIVE"
 }
 ]
}

I can display this just fine as a message, it will be displayed as a string. Of course it's not really pretty. So I want to convert it either to

a simple string so I can parse it into a flow for formatting, then returning a text string.
a formatted message/table, possibly using an adaptive card or other means without the need for a flow. (preferred)

I've been trying converting using Text(), JSON(), within variables and without, and a million other things, but I'm always running into errors. For example:

When using Text(): Expected text or number. We expect text or a number at this point in the formula.
When using a flow: There is an error: 'BindingIncorrectTypeError'

In Logic Apps / Power Automate Flows it's so easy to convert data types, but in Copilot it seems quite complicated. I appreciate the help!

Categories:

Flow Integration

All responses (8)

Answers (0)

Shadout7 Microsoft Employee on 18 Apr 2024 at 12:13:08

Like (0)

Report

Re: How to convert a KQL query result to simple text or a table?

@v-jefferni This is how it looks for me though.

Using a Power Automate flow for every KQL query seems counter-intuitive. Wouldn't that make the KQL query action somewhat limited when it can't display table results within Copilot itself?
v-jefferni on 18 Apr 2024 at 09:57:32

Like (0)

Report

Re: How to convert a KQL query result to simple text or a table?

Hi @Shadout7 ,

If you look at the variable properties, you will find in its data type that the table includes dynamic properties/columns with type "Any", and this is why we are not able to reference the column names in functions.

Please consider create a power automate flow to get the table and return the info you need in Text to copilot.

Best regards,
Shadout7 Microsoft Employee on 11 Apr 2024 at 11:58:19

Like (0)

Report

Re: How to convert a KQL query result to simple text or a table?

@v-jefferni Apologies, too many changes. Here are the current and correct nodes.
v-jefferni on 11 Apr 2024 at 09:59:05

Like (0)

Report

Re: How to convert a KQL query result to simple text or a table?

Hi @Shadout7 ,

The variable name was IcMIntern... in your previous reply, but at present it's IcMTriageQueue. Have you changed the name? Could you please add a message node and use the variable as output so that I can see what's the value of the variable now?

Best regards,
Shadout7 Microsoft Employee on 11 Apr 2024 at 09:42:47

Like (0)

Report
Re: How to convert a KQL query result to simple text or a table?

Hi @v-jefferni

Understood. But still not getting this to work.

{ type: "AdaptiveCard", version: "1.5", body: [ { type: "Container", items: ForAll(Topic.IcMTriageQueue.value, { type: "TextBlock", text: "- Count: " & Count & ", Status: " & Status & ", IsAtRisk: " & Boolean(IsAtRisk) & ", IsFailed: " & Boolean(IsFailed) & ", IsSev2: " & Boolean(IsSev2), wrap: "true" } ) } ] }

I've tried addressing the fields with Topic.IcMTriageQueue.Status, Topic.IcMTriageQueue.value.Status, and even 'Topic.IcMTriageQueue.value.Status' and 'Topic.IcMTriageQueue.Status'. All given different kinds of errors.
v-jefferni on 11 Apr 2024 at 01:30:54

Like (0)

Report

Re: How to convert a KQL query result to simple text or a table?

Hi @Shadout7 ,

It's already a record, no need to parse it again. You can consider my Var2 variable is the result of your KQL query.

Best regards,
Shadout7 Microsoft Employee on 10 Apr 2024 at 15:00:09

Like (0)

Report

Re: How to convert a KQL query result to simple text or a table?

Hi @v-jefferni. Thank you so much for the pointer and the detailed screenshots. I was able to reproduce it the exact same way. However, when using my actual record data type output from the KQL query, I still get the same error.
v-jefferni on 10 Apr 2024 at 09:12:33

Like (0)

Report
Re: How to convert a KQL query result to simple text or a table?

Hi @Shadout7 ,

Please follow the detailed tutorial documentation in below link:

Display Data from Arrays in Adaptive Cards - Microsoft Copilot Studio | Microsoft Learn

formula in Adaptive Card:

{ type: "AdaptiveCard", version: "1.5", body: [ { type: "Container", items: ForAll(Topic.Var2.value, { type: "TextBlock", text: "- Count: " & Count & ", Status: " & Status & ", IsAtRisk: " & Boolean(IsAtRisk) & ", IsFailed: " & Boolean(IsFailed) & ", IsSev2: " & Boolean(IsSev2), wrap: true } ) } ] }

Result:

Best regards,