the issue is mainly with how grouping & counting is being done in Microsoft Power Automate.
Why your count is always = 1
Your current logic:
- union(body('Select_2'), body('Select_2'))
This is only used to remove duplicates, NOT to count occurrences.
So:
But count is always 1 ❌ (because you’re not actually counting)
Correct Approach (Group + Count)
You need 2 steps:
Step 1: Extract clean AISRefNo (Fix your format issue)
Instead of returning:
Return plain text only
Update your Select action:
- AISRefNo → substring(item()?['Subject'], <start>, <length>)
OR better (dynamic):
- trim(replace(replace(item()?['Subject'],'AISRefNo:',''),'"',''))
Output becomes:
Step 2: Get unique AIS numbers
Keep your union():
- union(outputs('Select'), outputs('Select'))
This gives:
- [AIS2736, AIS0146, AIS2065]
Step 3: COUNT occurrences (important fix)
Now add:
➤ Apply to each (on unique AIS list)
Inside loop:
Filter array:
- From: outputs('Select')
- Condition: item()?['AISRefNo'] == items('Apply_to_each')
Compose (Count):
length(body('Filter_array'))
Append to array:
{
"AISRefNo": items('Apply_to_each'),
"Count": length(body('Filter_array'))
}
Final Output
- AIS2736 → 3
- AIS0146 → 2
- AIS2065 → 5
Clean Solution Summary
Fix 1: Remove prefix
- Use substring() or replace() → return only AIS value
Fix 2: Count correctly
Use:
- union() → unique values
- Filter array + length() → count
Thanks
Manish
