Skip to main content

Notifications

Calling Actions from Copilot Studio
Unanswered

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

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!

  • 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,

Helpful resources

Quick Links

Exciting News for Copilot Studio Communi…

Get ready to experience a whole new level of engagement with the Copilot Studio…

Celebrating the May Super User of the…

LaurensM is an exceptional contributor to the Power Platform Community…

Check out the Copilot Studio Cookbook…

We are excited to announce our new Copilot Cookbook Gallery in the Community…

Leaderboard

#1
renatoromao Profile Picture

renatoromao 6,459

#2
Pstork1 Profile Picture

Pstork1 1,995

#3
Expiscornovus Profile Picture

Expiscornovus 1,708

Leaderboard

Featured topics