Power Platform Community Forum Thread Details

Hi everyone,
I'm working on a slightly complex Power Automate flow involving a CSV file.
Consider the CSV that I shared contains thousands of rows and hundreds of unique training codes. Here's what I need the flow to do:

1. Count the total number of entries for each training code.

Example: Training Code 101 appears 50 times.

2. Count how many are attending vs. not attending for each code.

Example: Out of 50 entries for Code 101, 30 are marked as "Attend" and 20 as "Not Attend".

3. Evaluate the attendance:

If more than half are attending → Status = "Run as usual"

If less than half are attending → Status = "Must be Cancelled"

4. Send me an email summarizing each training code with the format:

Training Code: 101
Total Number: 50
Attending: 30
Not Attending: 20
Status: Run as usual

If the attendance is low, the Status will be:

Training Code: 101
Total Number: 50
Attending: 20
Not Attending: 30
Status: Must be Cancelled

The trigger will be when I email myself with the subject "Calculate training code", and I’ll attach the CSV file. Any suggestions or guidance on how to structure this flow efficiently would be much appreciated!

So far this is what I have done

Categories:

Building flows

Hi,

Step 1: Trigger - When a new email arrives

Trigger: "When a new email arrives (V3)"

Subject Filter: Contains "Calculate training code"

Has Attachment: Yes

Step 2: Get Email Attachments

Action: "Get attachments (V2)"

Message Id: From trigger output

Step 3: Parse CSV Content

Action: "Get attachment content"

Attachment Id: From previous step

Step 4: List Rows Present in a Table

Action: "List rows present in a table"

Location: OneDrive for Business

Document Library: OneDrive

File: Upload your CSV or reference the attachment

Table: Table1

Step 5: Initialize Variables

Create these variables at the top of your flow:

Variable 1: Training Codes Array

Name: varTrainingCodes

Type: Array

Value: []

Variable 2: Results Array

Name: varResults

Type: Array

Value: []

Variable 3: Email Body

Name: varEmailBody

Type: String

Value: "Training Code Analysis Results:\n\n"

Step 6: Get Unique Training Codes

Action: "Select" (Data Operations)

From: Output from "List rows present in a table"

Map:
- Key: item
- Value: item()?['training_code']

Step 7: Compose Unique Training Codes

Action: "Compose"

Inputs: union(outputs('Select'), outputs('Select'))

This removes duplicates from the training codes

Step 8: Apply to Each Training Code

Action: "Apply to each"

Select an output: Output from "Compose Unique Training Codes"

Inside the Apply to Each:

Step 8a: Filter Array for Current Training Code

Action: "Filter array"

From: Output from "List rows present in a table"

Condition: item()?['training_code'] is equal to items('Apply_to_each')

Step 8b: Compose Total Count

Action: "Compose"

Inputs: length(body('Filter_array'))

Rename: "Total Count"

Step 8c: Filter for Attending

Action: "Filter array"

From: Output from "Filter array for Current Training Code"

Condition: item()?['status'] is equal to Attend

Step 8d: Compose Attending Count

Action: "Compose"

Inputs: length(body('Filter_array_2'))

Rename: "Attending Count"

Step 8e: Calculate Not Attending Count

Action: "Compose"

Inputs: sub(outputs('Total_Count'), outputs('Attending_Count'))

Rename: "Not Attending Count"

Step 8f: Determine Status

Action: "Condition"

Left side: outputs('Attending_Count')

Operator: is greater than

Right side: div(outputs('Total_Count'), 2)

If Yes (More than half attending):

Action: "Set variable"

Name: varCurrentStatus

Value: "Run as usual"

If No (Less than half attending):

Action: "Set variable"

Name: varCurrentStatus

Value: "Must be Cancelled"

Step 8g: Compose Result Object

Action: "Compose"

Inputs:

json

{
  "training_code": @{items('Apply_to_each')},
  "total": @{outputs('Total_Count')},
  "attending": @{outputs('Attending_Count')},
  "not_attending": @{outputs('Not_Attending_Count')},
  "status": @{variables('varCurrentStatus')}
}

Step 8h: Append to Results Array

Action: "Append to array variable"

Name: varResults

Value: Output from "Compose Result Object"

Step 8i: Format Email Line

Action: "Compose"

Inputs: concat('Training Code: ', items('Apply_to_each'), ' Total Number: ', outputs('Total_Count'), ' Attending: ', outputs('Attending_Count'), ' Not Attending: ', outputs('Not_Attending_Count'), ' Status: ', variables('varCurrentStatus'), '\n')

Step 8j: Append to Email Body

Action: "Append to string variable"

Name: varEmailBody

Value: Output from "Format Email Line"

Step 9: Send Email Summary

Action: "Send an email (V2)"

To: Your email address

Subject: Training Code Analysis - @{utcnow()}

Body: @{variables('varEmailBody')}

Alternative Approach Using Expression (More Efficient)

If you want a more concise approach, you can use this expression in a single Compose action:

join(
  select(
    union(
      select(
        body('List_rows_present_in_a_table'),
        item()?['training_code']
      ),
      select(
        body('List_rows_present_in_a_table'),
        item()?['training_code']
      )
    ),
    concat(
      'Training Code: ', item(),
      '   Total Number: ', length(filter(body('List_rows_present_in_a_table'), equals(item2()?['training_code'], item()))),
      '   Attending: ', length(filter(body('List_rows_present_in_a_table'), and(equals(item2()?['training_code'], item()), equals(item2()?['status'], 'Attend')))),
      '   Not Attending: ', length(filter(body('List_rows_present_in_a_table'), and(equals(item2()?['training_code'], item()), not(equals(item2()?['status'], 'Attend'))))),
      '   Status: ', if(greater(length(filter(body('List_rows_present_in_a_table'), and(equals(item2()?['training_code'], item()), equals(item2()?['status'], 'Attend')))), div(length(filter(body('List_rows_present_in_a_table'), equals(item2()?['training_code'], item()))), 2)), 'Run as usual', 'Must be Cancelled')
    )
  ),
  '\n'
)

If I have answered your question, please mark it as the preferred solution ✅ . If you like my response, please give it a Thumbs Up 👍. Regards, Riyaz

Community site session details

Filtering Top 5 list on a CSV file

Step 1: Trigger - When a new email arrives

Step 2: Get Email Attachments

Step 3: Parse CSV Content

Step 4: List Rows Present in a Table