How to create send Email flow which including a Privot Table summary report from the Sharepoint List.

[Anonymous]

Hi, I'm very new for this community and just beginning learn by myself for Power automate.

I have my project to ask every professional users here.

Steps

1. I already have a survey form created on MS Forms (form including Score to calculate)

2. I already have a Automate flow to keep all responses to Sharepoint list.

3. I need to know how to create send Email flow which including a Privot Table summary report from the Sharepoint List.

Example for Email that I need.

PS. I tried to use Power BI but It's doesn't work because I don't have a Premium license that can't use for this function.

Can someone please give me an advise ,

Thank you.

[grantjenkins]

@Anonymous A few questions for you.

1. Are the Class and Trainer list columns just text columns, or choice columns?
2. What would you display in your Pivot Table if a trainer was across multiple classes? For example, if Mr. D taught Class A4 and A7? I'm assuming it would calculate the average across all their classes.
3. How many items would you expect to be in your SharePoint List over the next couple of years?
4. Would you only want to use the data from the current month using Survey Date?

Just working on a solution for you now.

[Anonymous]

Hi, @grantjenkins 

Thanks for replying me, and here are the answers for your questions.

1. Are the Class and Trainer list columns just text columns, or choice columns?
    It's Choice columns.
2. What would you display in your Pivot Table if a trainer was across multiple classes? For example, if Mr. D taught Class A4 and A7?
   The result would be show only Mr. D and the Score of 2 classes are average.
3. How many items would be in your SharePoint List?
   There's only this item in this list.
4. Would you want to only get a certain number of items based on some criteria? I would assume the list would grow over time.
  Now I have only this item in the list, and in the future I would create some flow to clear the record every 2 Months to prevent the file from becoming too large. 

[grantjenkins]

Hopefully this is what you're looking for. Note that I convert the JSON data to XML as part of the flow so we can use XPath to calculate across the rows.

For this example, I've used the following list. Class is using the Title field (renamed to Class). Trainer is Single line of text. Survey Class and Survey Trainer are Number. Note that I added a couple of extra rows (A5) that happened last month, so won't be used in the flow.

See full flow below. I'll go into each of the actions.

Get items will retrieve all the items where the Survey Date is the current month. It uses the following expression for the Filter Query. The expressions get the start of the current month and the start of the following month and check that the Survey Date is between those dates.

SurveyDate ge '@{startOfMonth(utcNow())}' and SurveyDate lt '@{startOfMonth(addToTime(utcNow(), 1, 'month'))}'

By default, Get items will only return a maximum of 100 items. If you have a lot (or expect to have a lot) of items in your list, you will need to go into Settings, turn on Pagination, and set a Threshold larger than the number of items you would expect to have in your list over the next few years.

XML (Compose) converts the data returned from Get items to XML so we can use XPath expressions. This makes it much easier to perform calculations across rows (removes the need for loops). The expression used is:

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))

Select is where we build the data for our table. Below are the expressions used. XPath in Power Automate is still a bit limited and doesn't include the average (avg) function, so we need to divide the sum by the count to get the average. We use formatNumber with 'N2' so our averages come back as numbers with 2 decimal places.

//From - gets the list of trainers and uses union to remove duplicates
union(xpath(outputs('XML'), '//root/value/Trainer/text()'), xpath(outputs('XML'), '//root/value/Trainer/text()')) 

//Trainer
item()

//Survey Class Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer="', item(), '"]/SurveyClass/text()) div count(//root/value[Trainer="', item(), '"]/SurveyClass/text())')), 'N2')

//Survey Trainer Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer="', item(), '"]/SurveyTrainer/text()) div count(//root/value[Trainer="', item(), '"]/SurveyTrainer/text())')), 'N2')

 

The Select above provides us with the scores for each trainer. However, we still need to calculate the average scores across all trainers. Total (Compose) builds a single object within an array, retrieving the totals using the following expressions.

formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyClass/text()) div count(//root/value/SurveyClass/text())')), 'N2')

formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyTrainer/text()) div count(//root/value/SurveyTrainer/text())')), 'N2')

The full code that you can copy/paste into Total is below:

[
  {
    "Trainer": "Average Score",
    "Survey Class Score": @{formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyClass/text()) div count(//root/value/SurveyClass/text())')), 'N2')},
    "Survey Trainer Score": @{formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyTrainer/text()) div count(//root/value/SurveyTrainer/text())')), 'N2')}
  }
]

Create HTML table uses the union of the two arrays - Select and Total. Effectively, it combines the averages for each trainer, and the total averages across trainers.

union(body('Select'), outputs('Total'))

Compose HTML table style has some CSS that will make the HTML table look nicer in the email. The CSS is below:

<style>  
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: center;
    }
    table td:first-child,
    table th:first-child {
        text-align: left;
    }
    table th, tr:last-child {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Finally, Send an email uses the output from both Compose HTML table style and Create HTML table. It also uses the following expression within the Subject and Body to display the current month.

formatDateTime(utcNow(), 'MMMM')

After running the flow, we should get the following output.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[grantjenkins · Accepted solution]

Because your Trainer column is of type Choice, we would need to change the expressions within the Select action. See updated expressions.

//From
union(xpath(outputs('XML'), '//root/value/Trainer/Value/text()'), xpath(outputs('XML'), '//root/value/Trainer/Value/text()'))

//Survey Class Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer/Value="', item(), '"]/SurveyClass/text()) div count(//root/value[Trainer/Value="', item(), '"]/SurveyClass/text())')), 'N2')

//Survey Trainer Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer/Value="', item(), '"]/SurveyTrainer/text()) div count(//root/value[Trainer/Value="', item(), '"]/SurveyTrainer/text())')), 'N2')

[Anonymous]

Hi @grantjenkins ,

Thanks for your help and show me all of fully flow, that's helpful for me very much.

I done it with my flow but I've got some problem and need your help again.

1. The email result don't show the data in the table.

    

2. All steps are run completely without any error.

   

3. The Select step I've got the result like this.

4. And the result of Total step like this.

5. Here are the steps that I follow from your flow with same column name in the table.

Select

//From
union(xpath(outputs('XML'), '//root/value/Trainer/Value/text()'), xpath(outputs('XML'), '//root/value/Trainer/Value/text()'))

//Survey Class Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer/Value="', item(), '"]/SurveyClass/text()) div count(//root/value[Trainer/Value="', item(), '"]/SurveyClass/text())')), 'N2')

//Survey Trainer Score
formatNumber(xpath(outputs('XML'), concat('sum(//root/value[Trainer/Value="', item(), '"]/SurveyTrainer/text()) div count(//root/value[Trainer/Value="', item(), '"]/SurveyTrainer/text())')), 'N2')

Total

[
  {
    "Trainer": "Average Score",
    "Survey Class Score": @{formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyClass/text()) div count(//root/value/SurveyClass/text())')), 'N2')},
    "Survey Trainer Score": @{formatNumber(xpath(outputs('XML'), concat('sum(//root/value/SurveyTrainer/text()) div count(//root/value/SurveyTrainer/text())')), 'N2')}
  }
]

6. Is it possible that the problem from Survey Class and Survey Trainer column are calculated with Average result from other columns like my sharepoint list below?

7. The Column type that I set for the table.

     Survey Class      Calculated (calculation based on other columns)
     Survey Trainer   Calculated (calculation based on other columns)

     Class Score...     Number

---

Thank you.