Untangling my first Power Automate Nightmare

The Problem

Four months into my job at a manufacturing company, I was wanting more. I'd been hired as a production technician, but my software engineering senses were tingling, and I was just itching to tackle some of the operational inefficiencies I saw all around me every day. So I started volunteering. Asking around. Making it known that if anyone had a technical problem, I wanted to hear about it.

The operations supervisor took me up on that. "I've got this project that's been giving me trouble. Think you could take a look?"

Sweet! My first project. This is going to be nice and easy.

The project was a Power Automate Flow he'd built for handling purchase order requests. Employees would submit a Microsoft Form, and the Flow would route it through an approval chain based on two factors: which department was requesting, and how much money they wanted to spend.

Simple enough, right?

The Requirements

Here's what the approval matrix looked like for a single department:

Okay. I got this, right?

Well, actually, the approval doesn't just go to the person at your threshold. It starts two levels below and chains upward. So if Alice submits a $75k purchase order:

1. Flow determines the threshold is "Under $100k"
2. Two levels below Earl is Charlie
3. Charlie gets notified and must approve
4. If approved, Dick gets notified and must approve
5. If approved, Earl gets notified and must approve
6. If Earl approves, Alice gets an email confirming the request is approved

Everyone in the chain plus the relevant purchasing manager gets CC'd on everything.

Okay, so now I've got it.

Well, actually, there were multiple departments, each with their own column of approvers. And sometimes the same person covered multiple price tiers.

The Original Implementation

The operations supervisor at the time had handled this complexity by... not abstracting it at all. To be fair, he wasn't a developer. He'd done what made sense: every possible path through the matrix was hardcoded. Department A, under $2k? Hardcoded branch. Department A, under $10k? Separate hardcoded branch. Department B, under $2k? You guessed it.

The Flow was so large it consumed roughly 3GB of RAM just to open in the browser. It would freeze regularly. If someone's email changed, you had to hunt through dozens of near-identical branches to update it everywhere. He was understandably frustrated.

And here's the thing: I had never touched Power Automate before in my life.

And I was in for quite the ride. The low-code world sure made it harder than it ever had to be.

But when has that ever stopped me? (Probably a couple of times, to be completely honest...)

Figuring Out What They Actually Wanted

Before I could fix anything, I had to understand what the system was supposed to do. This was the first time I had to communicate with multiple non-technical people who couldn't even agree with each other on what they wanted and often lacked the vocabulary to describe what was building in their head.

I scheduled meetings with stakeholders. I asked detailed questions. I got contradictory answers. The approval matrix existed as a Word document with a malformed table, but different people had different interpretations of how the chain logic worked. Did "two levels below" mean two rows up in the table, or two distinct people? What happens when the same person appears in consecutive rows? When is lunch?

While I may have snuck in that last one there, these were questions that needed to be answered. And most of the time, I had to take initiative to seek them out individually for questions. As a software engineer, I never expected the headache to be in gathering requirements more than anywhere else.

Eventually I got alignment. The rules were:

1. Look up the column (department) and find the row (threshold) that matches the request amount
2. The approver in that cell is the "final" approver
3. Walk up two rows to find the "starting" approver
4. Chain through each approver sequentially until you reach the final
5. Skip duplicates (if someone appears in consecutive rows, don't ask them twice)

The Rebuild

I decided to spare my brain a trip down Looney Lane. Like a floundering fish on the beach, this creature was suffering. I was going to start completely from scratch.

Data-Driven Configuration

Instead of hardcoding paths, I built a configuration layer that separated people from logic.

At the top of the Flow, a series of Compose actions defined each role:

Operations_Supervisor    →    bob@company.com
VP_of_Operations         →    charlie@company.com
Director_of_Finance      →    dick@company.com
CFO                      →    earl@company.com

These were just variables with friendly names. No JSON, no nesting. If Bob leaves and Susan takes over as Operations Supervisor, you update one line at the top of the Flow. Done.

The approval matrix then referenced these variables:

{
  "inventory": {
    "thresholds": [
      { "max": 2000, "approver": "@{outputs('Operations_Supervisor')}" },
      { "max": 10000, "approver": "@{outputs('VP_of_Operations')}" },
      { "max": 50000, "approver": "@{outputs('Director_of_Finance')}" },
      { "max": 100000, "approver": "@{outputs('CFO')}" }
    ]
  }
}

The operations supervisor didn't need to understand JSON to make personnel changes. He just scrolled to the top, found the role, and swapped the email. The matrix structure itself rarely changed, but people changed all the time.

The Lookup Logic

The first step was finding the right approvers for a given request. Given a department and amount:

1. Find the department in the config
2. Find the first threshold where amount <= max
3. That's your final approver index
4. Subtract 2 (clamped to 0) to get your starting approver index
5. Slice the array from start to final (inclusive)

In Power Automate, this meant composing JSON, using expressions to filter arrays, and liberal use of first(), skip(), and take() functions. Not intuitive if you're used to writing actual code, but workable.

The Approval Chain

Once I had the list of approvers, I needed to request approval from each one sequentially. Power Automate has an "Apply to each" action that loops over arrays, but the tricky part was handling the sequential dependency: approver 2 shouldn't be notified until approver 1 has approved.

The solution was a "Do until" loop with a tracking variable. Each iteration:

1. Get the current approver from the list
2. Send them an approval request via the Approvals connector
3. Wait for their response
4. If rejected, exit the loop and send rejection notifications
5. If approved, increment the index and continue

The deduplication (skipping consecutive duplicate approvers) was handled during the list-building phase. If the current approver email matched the previous one, don't add it to the chain.

The Full Matrix

With the pattern established, scaling to multiple departments was just a matter of expanding the config:

Notice that Operations and Engineering have the same person for the first two tiers. The system handles this automatically because the deduplication logic doesn't care how the duplicates got there.

What Made It Work

The final Flow was maybe 30 actions instead of hundreds. It opened in the browser without freezing. When the operations supervisor needed to update an approver, he could edit the emails himself without touching the Flow logic.

A few things that made the difference:

Compose actions for intermediate state. Power Automate's expressions are limited. Breaking complex logic into multiple Compose actions with intermediate results made debugging much easier than trying to do everything in one expression.

Generous use of variables. Tracking the current index, the approval status, the built-up approver list. Variables aren't free in Power Automate (they add latency), but the clarity was worth it.

Testing with edge cases first. I started by testing the scenarios that broke the old system: requests exactly at threshold boundaries, departments where one person covered multiple tiers, the minimum and maximum amounts.

Key Takeaways

Understand the problem before touching the code. I spent more time in meetings than in Power Automate. That's not a failure; that's the job. The stakeholders didn't fully agree on their own process until I forced the conversation.

Data-driven beats hardcoded. The original Flow wasn't wrong because the developer was bad. It was wrong because it encoded business logic as control flow instead of configuration. When the logic lives in data, non-developers can maintain it.

Sometimes you have to throw it away. I could have tried to refactor the existing Flow incrementally. It would have taken longer and the result would have been worse. Starting fresh was the right call.

Unfamiliar tools are learnable. I'd never used Power Automate. I learned it because the problem required it. Four months into a job, volunteering for anything technical I could get my hands on, handed a broken workflow built by someone who wasn't a developer.

The whole project took about 3-4 weeks of focused work, most of it in the first three weeks figuring out requirements and learning Power Automate's quirks. The last week was iterating over inconsistencies between stakeholders. The operations supervisor was happy. The Flow stopped crashing browsers.

And they are still using it to this day.

In the end, would I recommend using Power Automate for this much complexity? Absolutely not. But sometimes, you've gotta work with what they're using. And that is just how it goes.