Skip to main content

Notifications

Copilot Studio - Calling Actions from Copilot S...
Unanswered

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

(0) ShareShare
ReportReport
Posted on by

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 

  1. a simple string so I can parse it into a flow for formatting, then returning a text string.
  2. 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:
  • Shadout7 Profile Picture
    Shadout7 on at
    Re: How to convert a KQL query result to simple text or a table?

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

    Shadout7_1-1713442320828.png

     

    Shadout7_0-1713442203955.png

    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 Profile Picture
    v-jefferni on at
    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.

    vjefferni_0-1713434276235.png

     

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

     

    Best regards,

  • Shadout7 Profile Picture
    Shadout7 on at
    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.

    Shadout7_2-1712836751591.png

     

    Shadout7_0-1712840071600.png

     

     

  • v-jefferni Profile Picture
    v-jefferni on at
    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 Profile Picture
    Shadout7 on at
    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"
     }
     )
     }
     ]
    }

     

    Shadout7_0-1712828420525.png

    Shadout7_1-1712828453881.png

     

    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 Profile Picture
    v-jefferni on at
    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 Profile Picture
    Shadout7 on at
    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.

     

    Shadout7_0-1712761191192.png

     

  • v-jefferni Profile Picture
    v-jefferni on at
    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

     

    vjefferni_1-1712741513297.png

     

    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:

    vjefferni_2-1712741539277.png

    Best regards,

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,495

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,822

Leaderboard

Featured topics