Power Automate Desktop: Find One-to-Many Values Between Two Columns

The Problem

You have a table with two columns, A and B. You want to find every A value that is linked to more than one different B value.

✅ One-to-many	❌ Not one-to-many
Apple → Red, Green	Banana → Yellow, Yellow (same value repeated)
	Date → Brown (only one value)

No scripts needed. Only built-in Power Automate Desktop (PAD) actions.

---

Sample Data

A	B
Apple	Red
Apple	Green
Banana	Yellow
Banana	Yellow
Cherry	Red
Cherry	Black
Date	Brown
Fig	Purple
Fig	Green
Grape	Purple

---

How It Works (3 Simple Steps)

Step	What happens
1	Get the unique list of A values
2	For each A value, collect its unique B values
3	If there are more than 1 B values → add it to the result

---

Variables Used

Variable	Type	Purpose
InputDT	Data table	Source data (A, B)
DistinctAValues	List	Unique A values
OneToManyResults	Data table	Final result
CurrentRow	Data row	Row being read (Step 1)
CurrentAValue	Text	A value being checked
BValuesForThisA	List	Unique B values for current A
DataRow	Data row	Row being read (Step 2)
BValuesText	Text	B values joined as "Red, Green"

---

Build the Flow

Step 1 — Create the input table

Variables → Create new data table → add columns A and B → enter the sample rows → name it InputDT.

In real projects, use Excel → Read from Excel worksheet instead.

Step 2 — Get unique A values

Action	Setting
Create new list	Name: DistinctAValues
For each	Loop InputDT, item: CurrentRow
If	DistinctAValues Does not contain CurrentRow['A']
Add item to list	Add CurrentRow['A'] to DistinctAValues

Result: Apple, Banana, Cherry, Date, Fig, Grape

Step 3 — Create the result table

Variables → Create new data table → columns A Value and Matching B Values → name it OneToManyResults.

💡 The visual builder adds one empty row by default. Delete it in the builder (or add Clear data table right after) so your result doesn't start with a blank line.

Step 4 — Check each A value

Action	Setting
For each	Loop DistinctAValues, item: CurrentAValue
Create new list	Name: BValuesForThisA (inside the loop, so it resets each time)
For each	Loop InputDT, item: DataRow
If	DataRow['A'] Equal to CurrentAValue
If	BValuesForThisA Does not contain DataRow['B']
Add item to list	Add DataRow['B'] to BValuesForThisA
If	BValuesForThisA.Count Greater than 1
Join text	Join BValuesForThisA with ,  → BValuesText
Insert row into data table	Add [CurrentAValue, BValuesText] to OneToManyResults

Step 5 — Show the result

Message boxes → Display message → show OneToManyResults.

---

Output

A Value	Matching B Values
Apple	Red, Green
Cherry	Red, Black
Fig	Purple, Green

Banana, Date and Grape are skipped. ✔️

---

Quick Tips

Tip	Why
Create BValuesForThisA inside the loop	Otherwise old values carry over
Use %CurrentRow[0]% if no headers	Column index starts at 0
Good for small/medium tables	Nested loops slow down on thousands of rows

---

Complete Code

Copy the code below and paste it directly into the PAD designer (Ctrl + V).

Variables.CreateNewDatatable InputTable: { ^['A', 'B'], [$'''Apple''', $'''Red'''], [$'''Apple''', $'''Green'''], [$'''Banana''', $'''Yellow'''], [$'''Banana''', $'''Yellow'''], [$'''Cherry''', $'''Red'''], [$'''Cherry''', $'''Black'''], [$'''Date''', $'''Brown'''], [$'''Fig''', $'''Purple'''], [$'''Fig''', $'''Green'''], [$'''Grape''', $'''Purple'''] } DataTable=> InputDT
Variables.CreateNewList List=> DistinctAValues
LOOP FOREACH CurrentRow IN InputDT
    IF NotContains(DistinctAValues, CurrentRow['A'], False) THEN
        Variables.AddItemToList Item: CurrentRow['A'] List: DistinctAValues
    END
END
Variables.CreateNewDatatable InputTable: { ^['A Value', 'Matching B Values'], [$'''''', $''''''] } DataTable=> OneToManyResults
LOOP FOREACH CurrentAValue IN DistinctAValues
    Variables.CreateNewList List=> BValuesForThisA
    LOOP FOREACH DataRow IN InputDT
        IF DataRow['A'] = CurrentAValue THEN
            IF NotContains(BValuesForThisA, DataRow['B'], False) THEN
                Variables.AddItemToList Item: DataRow['B'] List: BValuesForThisA
            END
        END
    END
    IF BValuesForThisA.Count > 1 THEN
        Text.JoinText.JoinWithCustomDelimiter List: BValuesForThisA CustomDelimiter: $''', ''' Result=> BValuesText
        Variables.AddRowToDataTable.AppendRowToDataTable DataTable: OneToManyResults RowToAdd: [CurrentAValue, BValuesText]
    END
END
Display.ShowMessageDialog.ShowMessage Title: $'''One-to-Many Results''' Message: OneToManyResults Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

---

References

• Variables actions
• Loop actions
• Conditional actions
• Text actions
• Variable data types

Building a Serverless Microsoft 365 Message Center Monitor with Azure Automation

Microsoft 365 admins live in the Message Center — but "check the Message Center daily" is exactly the kind of task that should never depend on a human remembering. This post walks through a small, serverless watcher: an Azure Automation PowerShell runbook that reads Message Center posts via Microsoft Graph, filters for ones with an action-required-by date, and hands off anything new to a Power Automate flow for Teams and email delivery.

No VMs, no credentials to rotate, no local state file — just a managed identity, a Graph permission, and two Automation Variables doing the remembering for you.

Architecture at a Glance

Layer	Component	Job
Trigger	Azure Automation Schedule	Fires the runbook on a cadence (e.g. daily)
Compute	Azure Automation PowerShell 7.2 runbook	Reads Graph, filters, dedupes, calls the webhook
Identity	Automation Account System-assigned Managed Identity	Authenticates to Graph — no secrets stored anywhere
Data source	Microsoft Graph /admin/serviceAnnouncement/messages	Returns all Message Center posts for the tenant
State	Azure Automation Variables	Hold config + a JSON dedupe cache (the sandbox has no persistent disk)
Delivery	Power Automate HTTP-triggered flow	Posts an adaptive card to Teams and sends a digest email

Why split it this way instead of one giant script? The runbook's only job is "find what changed and describe it as JSON." Everything about how it looks in Teams or email lives in the Power Automate flow, so you can redesign the notification without touching the PowerShell at all.

Why Not the Built-in Power Automate Connector?

Power Automate ships a native "Microsoft 365 message center" connector — worth ruling out before building this. It only syncs posts into Planner; it has no Teams or email action. That gap is what justifies going straight to Graph.

Prerequisites

#	Requirement	Notes
1	An Azure Automation Account	PowerShell 7.2 runtime environment
2	Global Admin / Privileged Role Administrator access	Needed once, to grant the Graph permission below
3	Microsoft.Graph.Authentication module	Imported into the Automation Account's runtime environment
4	A Power Automate flow with an HTTP request trigger	Receives the JSON payload; builds the Teams card + email
5	10 minutes for Azure AD permission propagation	App role grants to a managed identity aren't instant — more on this below

Step 1 — Enable the Managed Identity

Automation Account → Identity → turn on System-assigned. This creates a service principal in Microsoft Entra ID that represents the Automation Account itself — no app registration, no client secret.

Step 2 — Grant the Graph Permission

The runbook needs the application permission ServiceMessage.Read.All on Microsoft Graph. This is an app-only, admin-consent-only permission — there's no portal blade for assigning Graph app roles to a managed identity, so it's a scripted step either way. Run one of the two options below, once, from an account with Global Admin or Privileged Role Administrator rights.

Option A — Azure CLI (az rest), e.g. from Azure Cloud Shell

No extra modules to install — az is already signed in wherever Cloud Shell runs.

# One-time: grant ServiceMessage.Read.All to the Automation Account's managed identity
$automationAccountName = "<your-automation-account-name>"

$mi       = az ad sp list --display-name $automationAccountName `
              --query "[0].{id:id}" -o json | ConvertFrom-Json
$graphSp  = az ad sp show --id 00000003-0000-0000-c000-000000000000 `
              --query "{id:id, approle:appRoles[?value=='ServiceMessage.Read.All'].id | [0]}" `
              -o json | ConvertFrom-Json

$body = @{
    principalId = $mi.id
    resourceId  = $graphSp.id
    appRoleId   = $graphSp.approle
} | ConvertTo-Json -Compress

az rest --method POST `
  --uri "https://graph.microsoft.com/v1.0/servicePrincipals/$($mi.id)/appRoleAssignments" `
  --headers "Content-Type=application/json" `
  --body $body

Option B — PowerShell 7 with the Microsoft Graph SDK

Same end result, using Microsoft.Graph.Applications cmdlets instead of raw REST calls. Useful if you're standardizing on Graph PowerShell everywhere and don't want an Azure CLI dependency.

# One-time: grant ServiceMessage.Read.All to the Automation Account's managed identity
# Requires the Microsoft.Graph.Applications module:
#   Install-Module Microsoft.Graph.Applications -Scope CurrentUser -Force

$automationAccountName = "<your-automation-account-name>"

Connect-MgGraph -Scopes 'Application.Read.All', 'AppRoleAssignment.ReadWrite.All' -NoWelcome

# The managed identity's service principal (same object as $mi.id in Option A)
$mi = Get-MgServicePrincipal -Filter "displayName eq '$automationAccountName'"

# Microsoft Graph's own service principal, and the ServiceMessage.Read.All app role on it
$graphSp  = Get-MgServicePrincipal -Filter "appId eq '00000003-0000-0000-c000-000000000000'"
$appRole  = $graphSp.AppRoles | Where-Object { $_.Value -eq 'ServiceMessage.Read.All' }

New-MgServicePrincipalAppRoleAssignment `
    -ServicePrincipalId $mi.Id `
    -PrincipalId        $mi.Id `
    -ResourceId         $graphSp.Id `
    -AppRoleId          $appRole.Id

If a Permissions requested consent dialog appears on Connect-MgGraph, choose Consent on behalf of your organization and accept — that's the admin-consent step for AppRoleAssignment.ReadWrite.All.

Either option returns the new appRoleAssignment object, including "resourceDisplayName": "Microsoft Graph" (or ResourceDisplayName in the PowerShell object) and your Automation Account's name as the principal. Give it 1–2 minutes before your first test run — directory replication lags slightly behind the write, and an immediate retry can still 403.

Step 3 — Import the Graph Module

Automation Account → Modules → Browse gallery → search Microsoft.Graph.Authentication → target it at your PowerShell 7.2 runtime environment. This can take several minutes the first time.

Step 4 — Create the Automation Variables

Variable name	Encrypted?	Example value	Purpose
MC_WebhookUrl	Yes	your Power Automate HTTP trigger URL	Signed URL — treat it like a credential
MC_LookaheadDays	No	14	Notify on messages due within N days
MC_OverdueGraceDays	No	30	Keep showing overdue messages for N days before dropping them
MC_WhatIfMode	No	true	true = print the payload only; false = actually POST
MC_StateJson	No	{}	Dedupe cache — the runbook updates this after every run

Type the webhook URL directly into the encrypted variable field in the portal. It carries a signature, so it shouldn't pass through any other script or log.

Step 5 — Create the Runbook

Automation Account → Runbooks → Create → PowerShell, runtime 7.2, paste in the script below → Save (leave unpublished until you've tested it).

The Complete Script

<#
.SYNOPSIS
    Azure Automation runbook - monitors Microsoft 365 Message Center posts that carry an
    "Action required by" date and posts new/changed items to a Power Automate flow for
    Teams + email notification.

.DESCRIPTION
    PowerShell 7.2 runbook. Authenticates with the Automation Account's managed identity -
    no interactive sign-in, no local state file. All configuration and the run-to-run
    dedupe cache live in Automation Variables, because the runbook sandbox has no
    persistent local disk between jobs.
#>

# ============================================================
# Step 0 - read configuration
# ------------------------------------------------------------
# $UseAutomationVariables = $true once the Variables below have been created.
# Until then it stays $false so you can test the Graph/dedupe/notify logic
# with direct values.
# ============================================================
$UseAutomationVariables = $false

if ($UseAutomationVariables) {
    $WebhookUrl       = Get-AutomationVariable -Name 'MC_WebhookUrl'
    $LookaheadDays    = [int](Get-AutomationVariable -Name 'MC_LookaheadDays')
    $OverdueGraceDays = [int](Get-AutomationVariable -Name 'MC_OverdueGraceDays')
    $WhatIfMode       = [System.Convert]::ToBoolean((Get-AutomationVariable -Name 'MC_WhatIfMode'))
} else {
    # TEMP direct values for testing - move these into Automation Variables and flip
    # $UseAutomationVariables to $true before this runs unattended on a schedule.
    $WebhookUrl       = 'https://<your-flow-environment>.environment.api.powerplatform.com/powerautomate/automations/direct/...'
    $LookaheadDays    = 14
    $OverdueGraceDays = 30
    $WhatIfMode       = $true
}

# ============================================================
# Step 1 - connect to Microsoft Graph using the managed identity
# ============================================================
Connect-MgGraph -Identity -NoWelcome

# ============================================================
# Step 2 - pull all current service messages (paginated)
# ============================================================
$uri = 'https://graph.microsoft.com/v1.0/admin/serviceAnnouncement/messages?$top=100'
$allMessages = [System.Collections.Generic.List[object]]::new()

while ($uri) {
    $page = Invoke-MgGraphRequest -Method GET -Uri $uri
    $allMessages.AddRange([object[]]$page.value)
    $uri = $page.'@odata.nextLink'
}

Write-Output "Retrieved $($allMessages.Count) total message(s) from Message Center."

# ============================================================
# Step 3 - keep only messages with an act-by date in range
# ============================================================
$now          = Get-Date
$horizon      = $now.AddDays($LookaheadDays)
$overdueFloor = $now.AddDays(-10 * $OverdueGraceDays)

$flagged = $allMessages | Where-Object {
    $_.actionRequiredByDateTime -and
    ([datetime]$_.actionRequiredByDateTime) -le $horizon -and
    ([datetime]$_.actionRequiredByDateTime) -ge $overdueFloor
}

Write-Output "Found $($flagged.Count) message(s) with an action-required date in range."

# ============================================================
# Step 4 - load the dedupe cache from the Automation Variable
# ============================================================
if ($UseAutomationVariables) {
    $stateJson = Get-AutomationVariable -Name 'MC_StateJson'
    if ([string]::IsNullOrWhiteSpace($stateJson)) {
        $state = @{}
    } else {
        $state = $stateJson | ConvertFrom-Json -AsHashtable
    }
} else {
    # TEMP - no persisted state while testing, so every flagged message looks "new".
    $state = @{}
}

# ============================================================
# Step 5 - build the list of messages that are new or changed since last run
# ============================================================
$toNotify = [System.Collections.Generic.List[object]]::new()

foreach ($msg in $flagged) {
    $lastKnown      = $state[$msg.id]
    $isNewOrChanged = -not $lastKnown -or ($lastKnown -ne $msg.lastModifiedDateTime)

    if ($isNewOrChanged) {
        $daysToAct = [math]::Round((([datetime]$msg.actionRequiredByDateTime) - (Get-Date)).TotalDays, 1)

        $plainSummary = ''
        if (-not [string]::IsNullOrWhiteSpace($msg.body.content)) {
            $plainSummary = [regex]::Replace($msg.body.content, '<[^>]+>', ' ')
            $plainSummary = [System.Net.WebUtility]::HtmlDecode($plainSummary)
            $plainSummary = ($plainSummary -replace '\s+', ' ').Trim()
            if ($plainSummary.Length -gt 320) {
                $plainSummary = $plainSummary.Substring(0, 320) + '...'
            } else {
                $plainSummary = $plainSummary
            }
        }

        $status = 'Upcoming'
        if ($daysToAct -lt 0) {
            $status = 'Overdue'
        } elseif ($daysToAct -le 7) {
            $status = 'DueSoon'
        } else {
            $status = 'Upcoming'
        }

        $toNotify.Add([pscustomobject]@{
            id                   = $msg.id
            title                = $msg.title
            services             = ($msg.services -join ', ')
            category             = $msg.category
            severity             = $msg.severity
            isMajorChange        = [bool]$msg.isMajorChange
            actionRequiredByDate = ([datetime]$msg.actionRequiredByDateTime).ToString('yyyy-MM-dd')
            daysRemaining        = $daysToAct
            status               = $status
            summary              = $plainSummary
            link                 = "https://admin.microsoft.com/#/MessageCenter/:/messages/$($msg.id)"
        })
    } else {
        continue
    }
}

Write-Output "$($toNotify.Count) message(s) are new or updated since the last run."

# ============================================================
# Step 6 - send to Power Automate (or preview if $WhatIfMode) and persist state
# ============================================================
if ($toNotify.Count -gt 0) {
    $payload = @{
        generatedAtUtc = (Get-Date).ToUniversalTime().ToString('o')
        messages       = $toNotify
    } | ConvertTo-Json -Depth 6

    if ($WhatIfMode) {
        Write-Output 'WhatIfMode is on - payload that would be POSTed:'
        Write-Output $payload
    } else {
        Invoke-RestMethod -Method Post -Uri $WebhookUrl -Body $payload -ContentType 'application/json'
    }

    foreach ($item in $toNotify) {
        $msgObj = $flagged | Where-Object { $_.id -eq $item.id } | Select-Object -First 1
        $state[$item.id] = $msgObj.lastModifiedDateTime
    }

    if ($WhatIfMode -or -not $UseAutomationVariables) {
        Write-Output 'WhatIfMode is on, or running with direct test values - state variable not updated.'
    } else {
        $newStateJson = $state | ConvertTo-Json -Depth 5 -Compress
        Set-AutomationVariable -Name 'MC_StateJson' -Value $newStateJson
    }
} else {
    Write-Output 'Nothing new to notify - done.'
}

# ============================================================
# Step 7 - disconnect (good hygiene; the sandbox is torn down after the job anyway)
# ============================================================
Disconnect-MgGraph | Out-Null

How the Logic Works

Concept	Implementation	Why
Lookahead window	$horizon = now + $LookaheadDays	Surfaces messages due soon, not the entire backlog
Overdue grace	$overdueFloor = now - (10 × $OverdueGraceDays)	Keeps recently-overdue items visible without re-alerting on ancient ones forever
Dedupe	Compare lastModifiedDateTime against the cached value per message id	A message only re-notifies if Microsoft actually edited it
Status buckets	Overdue / DueSoon (≤7 days) / Upcoming	Lets the Power Automate flow style the Teams card by urgency
HTML stripping	Regex strip tags + HtmlDecode + 320-char truncate	Message bodies are HTML; Teams/email summaries need plain text

Testing the Runbook

Use the Test pane (Edit Runbook → Test pane → Start) before publishing. With WhatIfMode = $true, a clean run prints something like:

Retrieved 100 total message(s) from Message Center.
Found 15 message(s) with an action-required date in range.
1 message(s) are new or updated since the last run.
WhatIfMode is on - payload that would be POSTed:
{
  "messages": [
    {
      "id": "MC000000",
      "title": "Example: A Microsoft 365 service is changing",
      "status": "DueSoon",
      "daysRemaining": 5.2,
      ...
    }
  ]
}

That's the signal everything upstream of the webhook — auth, permissions, filtering, dedupe — is working. Nothing has actually been posted anywhere yet.

Troubleshooting: Two Errors You Will Hit

Error	Cause	Fix
Variable not found. To create this Variable, navigate to the Variables blade...	Get-AutomationVariable called before the Variable exists	Either create the Variable, or set $UseAutomationVariables = $false and supply direct values while you finish setup
403 Forbidden on GET /admin/serviceAnnouncement/messages	Managed identity authenticated fine, but ServiceMessage.Read.All hasn't been granted (or hasn't propagated yet)	Run the Step 2 grant script; wait 1–2 minutes; retry the test

Both are expected, sequential milestones — not signs something is broken. If you see the 403 immediately after granting the permission, that's Azure AD replication lag, not a wrong app role ID.

Going Live Checklist

•  ServiceMessage.Read.All granted to the managed identity and confirmed via a successful test run
•  All five Automation Variables created, MC_WebhookUrl marked Encrypted
•  Script updated: $UseAutomationVariables = $true
•  MC_WhatIfMode flipped to false only after you've reviewed a WhatIf payload
•  Runbook Published (not just saved as draft)
•  A Schedule linked to the runbook (daily is a reasonable starting cadence)
•  Power Automate flow tested end-to-end with a sample payload

Extending It

• Add a services filter so you only get alerted for the workloads you own (Exchange, Teams, SharePoint, etc.)
• Route Overdue items to a different Teams channel than Upcoming ones
• Log every run's payload to a Storage Account or Log Analytics workspace for an audit trail
• Swap the single webhook for multiple flows — one per admin team — keyed off the services field

---

This pattern — Graph read, Automation Variables for state, a thin webhook to Power Automate for delivery — generalizes well beyond Message Center. The same shape works for app registration secret expiry, license usage thresholds, or any other "check something on a schedule and tell a human if it matters" automation.