Automating Business Processes with Power Automate — Cloud Flows, Desktop Flows, and Robust Error Handling

“Every morning, someone collects CSV files from a shared folder, rolls them up in Excel, and emails the result to a manager.” “Values get copy-pasted by hand from several business systems just to produce one summary.” We get asked to automate work like this with Power Automate fairly often these days.

The first small flow usually comes together in under an hour. The trouble starts after that. The flow runs fine for a while, then occasionally stops with an error once it’s in production. A minor layout change on the target system’s screen breaks it. Nobody settled on where the password should live, so it’s still typed directly into an input variable “just to get it working.” There’s a “working flow,” but when something goes wrong, no one is quite sure who can fix it. We’ve seen this pattern play out more than once.

Power Automate has a low barrier to entry — no-code or low-code, build-as-you-go — and that same ease is exactly what makes it easy to push a flow into production before its design has been thought through. This article walks through the parts that tend to bite teams in practice: when to choose cloud flows versus desktop flows, how the tool divides labor with PowerShell and VBA, error handling, stabilizing UI automation, keeping credentials safe, governance and operations, and where Power Automate’s scope ends and something else should take over.

1. The short version

• Power Automate broadly splits into two families: cloud flows, which connect cloud services through connectors, and desktop flows, an RPA capability that automates Windows screens and desktop applications. Deciding which one you actually need is the first design decision.
• If the goal is purely “automate a routine task on a PC” and screen interaction (clicking, typing) isn’t strictly required, PowerShell is often a better starting point than Power Automate Desktop — it’s easier to maintain and fits naturally into version control.
• Power Automate earns its keep when you need to operate the screen of an existing system that has little or no API, or when you want to quickly build notification/approval workflows around Microsoft 365 connectors (Outlook, SharePoint, Teams, and so on).
• Before a flow goes to production, four things need to be designed in from the start, not bolted on afterward: error handling (On Block Error), stable UI selectors, safe credential handling, and governance through DLP policies.¹²
• Unattended execution requires a different license and a different set of prerequisites than attended execution. Defer the licensing question and you risk a flow that works in a test environment but can’t be deployed to production.³⁴

2. The shape of Power Automate

Power Automate isn’t a single product so much as an umbrella over several automation mechanisms.

flowchart TB
    PA[Power Automate]
    PA --> Cloud[Cloud flows]
    PA --> Desktop[Desktop flows / RPA]
    PA --> Process[Process mining]
    PA --> Builder[AI Builder]

    Cloud --> Auto[Automated flow<br/>event-triggered]
    Cloud --> Sched[Scheduled flow<br/>recurring]
    Cloud --> Instant[Instant flow<br/>manual / button]
    Cloud --> Connectors[Connectors<br/>SharePoint / Outlook / Teams / SQL, etc.]

    Desktop --> Attended[Attended<br/>runs in front of the user]
    Desktop --> Unattended[Unattended<br/>runs on a dedicated PC/server]
    Desktop --> UIAuto[UI automation<br/>screen control, legacy app integration]

• Cloud flows connect cloud services through connectors. Depending on the trigger type, they split into automated flows (event-driven), scheduled flows (recurring), and instant flows (manually started).
• Desktop flows are RPA (Robotic Process Automation) that directly control applications and screens on Windows. They can be invoked from a cloud flow or run standalone.⁵
• Desktop flows further split into attended execution, which runs with a person in front of the screen, and unattended execution, which runs on a dedicated PC or server with no human involvement.⁴

A “dedicated machine” for unattended execution means more than just unlocked. On Windows 10/11, an unattended run fails if any user has an active Windows session at all — even just a locked one, and even if it isn’t the connection’s own user; on Windows Server, the narrower case of a locked session for that same connection user blocks the run too. The fix is to sign everyone out completely rather than locking or disconnecting, which is easy to get wrong after routine maintenance or RDP access by a different admin.⁶

When we get an automation request, the first move is usually just separating “is this connecting cloud services together” from “is this operating a screen” — that alone clarifies most of the design.

3. Cloud flows vs. desktop flows vs. PowerShell vs. VBA

Even though all four fall under “automation,” their strengths differ substantially.

A common misconception is treating “needs automation” as automatically meaning “needs Power Automate.” Driving an existing system’s UI with Power Automate Desktop when that system already exposes an API is usually a case where calling the API directly from PowerShell or .NET would be more stable. Conversely, when there’s no API for an old line-of-business system or a Win32 app and the screen is the only way in, desktop flow UI automation is a realistic choice.

Side note: the constraints of Excel and VBA, and how to think about replacing them, are covered separately in What Are VBA’s Limits, and How Realistic Migration Paths Look, which also touches on combining Office Scripts with Power Automate for Microsoft 365 workflows.⁷

4. Understanding licensing

Power Automate licensing splits broadly into per-user licenses and per-flow (process) licenses.³

The part that trips people up: which license applies depends on how a flow runs, not who built it. Automated and scheduled flows run under the flow owner’s license context, while button-triggered instant flows run under the invoking user’s license context. If unattended execution is part of the plan, verify the licensing requirements while still in the proof-of-concept stage — discovering them right before go-live is a common way projects stall. Note that the older standalone Unattended RPA add-on is now considered legacy: it has been replaced by the Power Automate Process license (existing Unattended RPA add-ons still work and are treated as equivalent to Process licenses, but new unattended capacity should be allocated as Process licenses).³⁸⁴

One thing worth flagging: a Process or Hosted Process capacity license doesn’t substitute for a user license. Allocating a Process license to a machine for unattended execution still requires that machine to have been registered by a user holding a Power Automate Premium license. And when a cloud flow running under a Process license triggers an attended or unattended desktop flow run, the user whose connection is used for that run still needs a Premium license (or another user license with desktop-flow entitlement). It’s easy to budget only for capacity licenses and then get blocked during testing because no one accounted for the user licenses underneath them.³

5. A worked design — aggregating CSV files into an Excel report and emailing it

As a concrete example, consider the common “produce a daily report” task.

Goal

1. Collect that day’s CSV files from a shared folder
2. Aggregate the contents into an Excel report template
3. Save the report to a designated folder
4. Email the result to stakeholders
5. On failure, notify someone and record what went wrong

flowchart TD
    Start([Scheduled trigger 06:30]) --> Block[[On Block Error: aggregation]]
    Block --> List[List files in folder<br/>get today's CSVs]
    List --> Check{Any files found?}
    Check -- No --> NoticeEmpty[Record empty result and exit]
    Check -- Yes --> Read[Read from CSV file<br/>loop per file]
    Read --> Agg[Aggregate values into variables]
    Agg --> Excel[Launch Excel<br/>open the template]
    Excel --> Write[Write to Excel worksheet<br/>write aggregated values]
    Write --> Save[Save and close Excel]
    Save --> Mail[Send an email<br/>send report to stakeholders]
    Mail --> Log[Append run results to log file]
    Log --> End([Completed successfully])
    Block -. error .-> Handler[Error handler]
    Handler --> CloseExcel[Close Excel if an instance is still open]
    CloseExcel --> LogErr[Record the error in the log]
    LogErr --> Notify[Notify the administrator]
    Notify --> End2([Recorded as a failed run])

The main actions and their role in this flow:

Rather than building the entire flow as one long list of actions, splitting it into subflows — file collection, aggregation, Excel writing, notification, logging — makes later edits and isolated re-runs much easier. Triggering on a schedule from a cloud flow while delegating the actual work to a desktop flow is also a structure that tends to age well operationally.⁵

6. Designing error handling

Power Automate Desktop offers On Block Error, which applies a shared error-handling policy across every action inside a block, instead of configuring “what happens on error” individually for each action.¹

[On Block Error] Aggregation block
  ├─ List files in folder
  ├─ Read from CSV file (loop)
  ├─ Launch Excel / Write to Excel worksheet
  └─ Save Excel / Close Excel

[Error handler]
  ├─ Use the Get last error action to capture the error's name, location, action, and message into a variable
  ├─ Close Excel if an instance is still open (don't skip cleanup just because something failed)
  ├─ Append the details to the log file
  ├─ Send an email (V2) to notify the administrator
  └─ Choose "resume from the end of the block" or "stop the flow run"

A few things worth keeping in mind:

• An individual action’s own error handling takes precedence over the block’s. Use individual settings for the one action that genuinely needs different behavior, and let On Block Error cover everything else.¹
• “Retry action if an error occurs” lets you automatically retry, a set number of times and at a set interval, for transient errors such as network latency or a locked file. Treating every error as retryable just wastes time, so distinguish errors worth retrying from ones that aren’t (data inconsistencies, for example, generally aren’t).¹
• To inspect the most recent error inside the handler, add an explicit Get last error action. It returns an error-type variable with six properties — name, location, the failing action, the subflow it belongs to, details, and message — rather than being available as some implicit variable. Enable its Clear error option afterward so a stale error value can’t accidentally get reused later in the flow.¹
• Logs should capture more than success/failure — record the item count, the file involved, and the error message. After the fact, the log is often the only clue to what actually went wrong.
• Explicitly choose whether the block should “resume from the end of the block” or “stop the flow run” after an error. Stopping while an Excel instance is still open leaves a process hanging around for the next run, so cleanup (closing instances) deserves attention precisely when something goes wrong.¹¹

If you’re used to try/catch/finally in PowerShell, it helps to map the On Block Error block to try, the error handler to catch, and the cleanup steps to finally.

7. Stabilizing UI automation

The single biggest cause of flaky desktop flows is usually how UI elements are identified — the selector.

• By default, the UI element picker records an element as a selector — a combination of attributes. If that selector includes attributes that change easily, such as a sequential index or a dynamically generated ID, the flow can break even when the screen looks unchanged.¹²
• Switching value-dependent attributes from Equals to Contains or a regular expression, and turning attributes that depend on a prior action’s result into variables, produces selectors that are more dynamic and less brittle.¹²
• Configuring multiple selectors lets the flow automatically fall back to the next one if the first fails. Worthwhile insurance for any operation that matters.¹²
• When a selector does break, Repair selector can generate a fix candidate automatically — worth trying before rebuilding a selector from scratch.¹²
• Screen transitions and application launches take time. Rather than relying solely on a fixed Wait, combine condition-based waits — “wait for window,” “wait until UI element is found” — with retry settings for when the element isn’t found right away.¹³
• For automation targets that simply won’t stabilize (virtualized screens, screens whose layout changes constantly), don’t force a UI-based solution — check first whether an API, file exchange, or direct database access offers a more stable path.

Getting a UI automation to run once isn’t hard. Whether it’s still running unbroken six months later comes down almost entirely to how the selectors were built.

8. Keeping credentials safe

Credentials — passwords, API keys, connection strings — are where automation projects most often go wrong.

• Avoid writing passwords or connection details directly into a flow’s input variables. The Get credential action retrieves credentials securely from a Power Automate credential backed by an external secret vault (Azure Key Vault or CyberArk); the retrieved value is marked sensitive and doesn’t appear in the flow’s run logs.¹⁴¹⁵
• Using Azure Key Vault as the secret store lets you centralize the connection information for Key Vault itself, instead of scattering credentials across every individual flow.¹⁵
• Provision a dedicated account for unattended execution, scoped to the minimum it actually needs, rather than reusing an account a person logs in with day to day. The broader the account’s permissions, the larger the blast radius of any bug or misconfiguration in the flow.
• It’s tempting to type a password in plaintext “just to get it working” during early testing — and that plaintext value has a way of surviving all the way into production. Building the habit of using Get credential from the very start of testing is the safer default.

9. Governance and operations

Once flows built by individuals or small teams start multiplying, organization-wide controls become necessary.

• Data loss prevention (DLP) policies classify the connectors available to flows and apps as “business data only,” “no business data,” or “blocked,” and prevent a single flow from combining a business-data connector with a no-business-data connector. This is the first governance layer worth setting up, since it’s what stops organizational data from leaking out through an unexpected connector combination.¹⁶
• Desktop flow actions can be classified and blocked under the same DLP framework, but that coverage isn’t on by default. An admin has to turn on the “Show desktop flow actions in DLP policies” tenant setting first — a one-way switch that can’t be reversed once enabled. Even after that, only the modules and actions a policy explicitly classifies are actually governed, so having a DLP policy in place doesn’t automatically mean every desktop flow action is under control.²
• Group unattended-execution machines into machine groups so it’s clear which flows run on which machines.
• Run history is visible from Power Automate’s admin and monitoring views. Building failure alerts — a Teams message or email — directly into the flow itself means an outage gets noticed without anyone needing to go check manually.
• When a cloud flow needs to reach an on-premises resource — a database or file share inside the corporate network that the cloud can’t reach directly — an on-premises data gateway bridges the two without requiring any inbound port to be opened; only an outbound connection is needed.¹⁷
• Separating test and production environments, and keeping desktop flows and connections scoped per environment, prevents changes made during testing from bleeding into production flows.

10. Where Power Automate’s limits are, and when to escalate

Power Automate is powerful, but not unbounded. Consider switching to PowerShell or a .NET application when:

When a Power Automate flow has grown organically into something genuinely complex, the better move is usually not to keep extending it, but to pull the core logic out into .NET or PowerShell and let Power Automate focus on triggering and notification. A worked example of automating log investigation and archiving in PowerShell is covered separately in Applied PowerShell Scripting: Safely Automating Log Investigation, Archiving, and Reporting.

11. Summary

Power Automate is genuinely easy to get started with. That same ease is also the problem: it lets a flow run long before its design has caught up. Whether a production flow stays maintainable mostly comes down to how many of the unglamorous decisions got made early.

Deciding up front how cloud flows, desktop flows, PowerShell, and .NET should divide the work saves a lot of rework later. Error handling, UI selectors, credentials, licensing, and DLP policy are all far cheaper to design in from the start than to retrofit once something breaks in production. And being willing to pull core logic out into .NET or PowerShell once a flow has outgrown itself, rather than stretching it further, is often what determines whether it’s still maintainable a year on.

There’s a real design gap between “a flow that happens to work” and “a flow you can hand off and trust.” That gap matters most exactly where automation tends to be requested in the first place: around existing business systems and Excel/VBA assets that already carry years of accumulated process knowledge.

Related articles

• What Are VBA’s Limits, and How Realistic Migration Paths Look
• Applied PowerShell Scripting: Safely Automating Log Investigation, Archiving, and Reporting
• How to Build Excel Report Output - A Decision Table for COM Automation / Open XML / Template Approaches

Related consulting areas

KomuraSoft LLC helps teams automate and modernize incrementally while keeping existing Excel, VBA, and Windows business assets in place, including design reviews for automation platforms built on Power Automate.

• Legacy Asset Reuse & Migration Support
• Technical Consulting & Design Review
• Windows App Development
• Contact

References

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These topic pages place the article in a broader service and decision context.

Where This Topic Connects

This article connects naturally to the following service pages.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and investigations into failures that are difficult to reproduce.