Group by & Aggregate using Custom AI Prompt

Use Case:

To generate reports, we need to group data by some columns and then find the aggregate like count, sum, min, max etc. The process of grouping & finding aggregate is not straight forward in Power Automate, we can achieve this by using complex expression, select & loop actions. To simplify this process, we can leverage AI prompt that will perform all those complex steps and return a Json array. We can use output Json array in generating html tables, csv files, excel files etc. as per our need. 

Scenario:

In SharePoint list, we have help desk ticket data. We need to export the below view in an email for reporting purpose:

Solution:

We will create a custom AI prompt that will group data & find the aggregate count for each category. In AI prompt, we will specify instructions to process input data & return in Json array format.

AI Prompt:

Create a new custom AI prompt and name it as 'Aggregate count with Group by prompt':

Enter two input parameters 'InputArray' & 'GroupByColumn':

Next, Choose output as 'JSON' format:

Select 'GPT 4' in 'Model' dropdown under Settings:

Add below prompt in the textbox:

Group & count by property and return an array 'result' with Count property. In the end, add an object in 'result' array with text 'Total' in property and sum of all elements in Count property:

To place the input parameters at the appropriate place, click 'Insert' button and select input parameter from the pop up window:

Place all input parameters as shown below:

To test the prompt. Enter the below text in 'InputArray' parameter:

[
{
"Status": "New"
},
{
"Status": "In progress"
},
{
"Status": "In progress"
},
{
"Status": "Completed"
}
]

Enter 'Status' in the 'GroupByColumn' parameter:

Click 'Test prompt' button & check the result:

Save the prompt by clicking 'Save custom prompt' button.

Power Automate:

Now, we will create a cloud flow and use the above custom AI prompt to group data. 

1. Add "Get items" action to get items from SharePoint list. Set the Site URL & list name:

2. Add "Select" action to create an array of object that contains the status value. Pass 'Value' property from the output of get items in 'From' parameter. Choose 'Status Value' from the dynamic window for 'Status' map value:

3. Now, add "Create text with GPT using a prompt" action. Choose 'Aggregate count with Group by prompt' custom prompt from drop down. Pass the output of Select action to 'InputArray' parameter. Enter 'Status' in the 'GroupByColumn' parameter:

4. Add "Create HTML table" action to create a table that can be shared in an email. To get the resultant array from the output of chat gpt prompt action we need to write an expression. Expression needs to be added in the expression box as shown below:

outputs('Create_text_with_GPT_using_a_prompt')?['body/responsev2/predictionOutput/structuredOutput']?['response']?['result']

Choose 'Custom' in 'Columns' drop down. Enter 'Status' as plain text in header and for value add an expression:

item()?['Status']

Similarly, enter 'Count' as plain text for header in next row and add an expression for its value:

item()?['Count']

 

5. Finally, send an email to recipient(s) using "Send an email" action. Pass the output body of "Create HTML table" action in the email body:

 

 

Output:

The output email will look like this:

 

 

Conclusion:

Following are the take aways:

• We can leverage custom AI prompt to solve complex coding problem in Power automate.
• Custom AI accepts can be reused for similar problem by passing different data in input parameters
• The output Json return by custom AI prompt can be used to generate html table, csv files or excel sheets.
• We can share custom AI model with other users so that they can reuse it in the apps or flow as per the need.