Power Automate Fundamentals

💡 Learning Outcomes

Explain what Power Automate is useful for, and where it is a poor fit.
Identify the main connector types Power Automate can handle.
Build a simple cloud flow that passes data between Microsoft 365 services.
Use expressions and internal functions to clean, format, and route values.
Add an approval step and handle approval and rejection outcomes.
Design a personalised email workflow using Excel rows and Word template content.

❓ Questions

What kinds of business processes are good candidates for automation?
How do triggers and connectors shape what a flow can do?
How can expressions make flows more reliable without making them unreadable?
How should approvals be recorded and communicated?
How can we send personalised emails without creating an unsafe bulk-mail process?

Structure & Agenda

Automating business processes with Power Automate (~10+10 min)
Triggers and flow start points (~10+15 min)
Connectors and data movement (~10+15 min)
Expressions, functions, and routing (~10+15 min)
Approvals, Excel tables, and template emails (~10+15 min)

🔧 5 activities spaced throughout the session.

Automating Business Processes with Power Automate

What is Power Automate?

Power Automate is Microsoft’s workflow automation tool for connecting apps, data, documents, and people. It lets you build flows that respond to an event and then carry out a sequence of actions.

For example, a flow might:

Capture a request from a form;
Add the request to a tracker;
Ask someone to approve it;
Send an email or Teams message;
Update a record when the work is complete.

💬 Think of Power Automate as a way to turn a repeatable administrative process into a visible, testable workflow.

Where Power Automate Fits into the wider Microsoft Ecosystem

Power Automate is one part of the Power Platform.

Power Automate handles workflow automation; the other tools focus on apps, reporting, and websites.

🧩 The key distinction is that Power Automate moves work between systems.

Main Parts of a Flow

Most flows follow the same basic shape.

A flow starts with a trigger, performs actions, may branch, and usually records or communicates an outcome.

🔁 When debugging, inspect the flow in this order: trigger, data, decision, output.

Decide Whether Automation Fits

Power Automate is a workflow automation tool. It is useful when a process has a repeatable pattern, a clear start point, and predictable next steps.

Good fit

Repeatable steps
Clear start event
Structured data
Predictable decisions
Known record location
Named owner

Poor fit

Complex judgement every time
Messy or unstructured inputs
No official record
Very high transaction volume
No owner after launch
No access to the source system

Should I Automate It?

A useful first question is not “can this be automated?” but “what decision, record, or communication should happen every time this event occurs?”

What event starts the work?
Is the input data structured enough to trust?
Where will the outcome be written?
Who fixes it when it fails?

💬 If one of those answers is unclear, do a smaller manual or semi-automated version first.

Understand Triggers

The trigger controls the first assumptions the flow makes.

Different trigger choices affect timing, available data, permissions, and failure points.

🚦 Choose the trigger that matches the real-world start of the process.

Types of Triggers

Trigger pattern	Example	Useful when
Manual	Manually trigger a flow	A person chooses when the process starts
Scheduled	Recurrence	Work should happen at a known time
Event-based	When a file / form response is created	A system event should start the work
Email-based	When a new email arrives	Requests arrive in a mailbox

🧪 The safest first build is usually manual. Once the logic works, you can replace the trigger with a form, email, SharePoint, or scheduled trigger.

Questions Before Choosing a Trigger

Before choosing a trigger, ask:

What exact event should start the process?
Does the event contain all data the flow needs?
Could the same event happen twice?
Should the flow run immediately, or is a scheduled batch acceptable?
Does the trigger use a personal connection, shared mailbox, service account, or system connection?
What happens when the person who created the flow leaves the team?

🧩 Trigger choice is a governance choice. It affects permissions, ownership, auditability, and support.

Task 1: Build a Triggered Request Flow (10 min)

Objective: Create a manually triggered flow and inspect the data made available by the trigger.

Exercise
Check

Open Power Automate and create an Instant cloud flow.
Name the flow Test flow - your initials.
Choose Manually trigger a flow.
Add trigger inputs for requester name, requester email, request type, requested-by date, and notes.
Add a Compose action called Request summary.
Build a short readable summary from the trigger fields.
Save and test the flow

Your run should show:

A successful trigger step;
The values entered in the test panel;
One successful compose step;
A readable request summary in the compose output.

Connect Power Automate to Other Services

What Connectors Do

A connector is the integration between Power Automate and another service.

Connectors provide:

Triggers, which start flows;
Actions, which do work after the flow has started;
Authentication, which controls what the flow is allowed to access;
Dynamic content, which exposes values from one step to later steps.

📬 For example, the Outlook connector can trigger a flow when an email arrives and can also send an email later in the same flow.

What Power Automate Can Connect To

Connectors are what let a flow work across services.

Common connector groups include Microsoft 365, data stores, documents, communication tools, apps, and APIs.

🔌 A connector is useful only if the connection has the right permissions.

Types of Connectors

Power Automate can handle many connector types.

Connector type	Examples	Common use
Microsoft 365	Outlook, Teams, SharePoint, OneDrive	Everyday collaboration workflows
Data	Excel Online, SharePoint lists, Dataverse, SQL Server	Reading and writing structured records
Documents	Word Online, OneDrive, SharePoint, PDF services	Creating, moving, and converting files
Communication	Outlook, Teams, notifications	Sending messages and alerts
AI and a lot more!	AI Builder, text analysis, document processing, external services	Extracting or transforming and disseminating content

💳 Licensing can affect availability. Premium connectors may not be available to everyone.

Connector Permissions

Each connector action uses a connection. A connection is the signed-in identity that Power Automate uses to access a service.

This matters because:

A flow may stop working when the owner’s password changes;
A flow may lose access when the owner leaves;
A personal mailbox connection may send messages from the wrong person;
A shared mailbox may be more appropriate than a personal mailbox;
A team-owned SharePoint site may be more maintainable than a personal OneDrive folder.

🧩 For any flow that matters, document who owns it, which connections it uses, and where its official records are stored.

Service Accounts

For important workflows, avoid relying on one person’s account where possible.

Better options may include:

A shared mailbox for team email;
A team-owned SharePoint site for files and lists;
A dedicated service account where your organisation allows it;
A named flow owner group rather than one individual.

🔐 Service accounts need governance. They should have only the access the flow needs, an owner, a support route, and a documented password or credential process.

Task 2: Connector Triage and First Notification (15 min)

Objective: Add an Outlook action and inspect the connection used to send the message.

Exercise
Discussion

Open the flow from Task 1.
Add Send an email after Request summary.
Choose the Office 365 Outlook action if it is available.
Send the email to yourself, using Request summary as the message body.
Save and test the flow.
Confirm that the email arrived.
Open run history and inspect the Outlook action inputs.
Sketch a connector map with one row per flow step.

Which connector creates the biggest maintenance or data protection risk?
Who should own the connection used to send email?

Cleaning and Routing Data

What Are Expressions?

Expressions are small formulas inside Power Automate. They are used when dynamic content needs to be changed before the next step uses it.

Expressions can clean text, format dates, combine values, and prepare data for the next action.

🧮 Use expressions when dynamic content is close, but not quite in the shape you need.

Text and Date Functions

Function	Use	Example pattern
`concat()`	Join text	`concat('Request: ', variables('Title'))`
`formatDateTime()`	Format a date	`formatDateTime(utcNow(), 'dd MMM yyyy')`
`toLower()`	Standardise text	`toLower(triggerBody()?['text'])`
`replace()`	Replace text	`replace(value, '/', '-')`
`split()`	Split text into parts	`split(value, ';')`

🧹 Text and date functions are usually used to make messy input consistent before the next step uses it.

Logic and List Functions

Function	Use	Example pattern
`coalesce()`	Fallback for blanks	`coalesce(triggerBody()?['text'], 'Not provided')`
`if()`	Return one of two values	`if(equals(x, y), 'Yes', 'No')`
`equals()`	Compare values	`equals(toLower(value), 'urgent')`
`contains()`	Check whether text includes a value	`contains(toLower(value), 'urgent')`
`length()`	Count characters or items	`length(outputs('List_rows')?['body/value'])`

🔎 Logic and list functions help the flow choose a path, check for missing data, or count what came back from a connector.

Keep Expressions Readable

The whole point of Power Automate is that it is a low-code or no-code solution to automation.

To keep expressions maintainable, users should:

Use a separate compose action for each important transformation;
Rename compose actions after what they produce;
Avoid nesting too many functions in one expression;
Add a short note in the action name if the logic is not obvious.

🏷️ For example, Compose normalise request type is clearer than Compose 2.

Handle Missing Values

User input is often incomplete. A flow should not assume that every value exists.

Common defensive patterns include:

Using required fields in the trigger or form where possible;
Using coalesce() to provide safe fallback text;
Using a condition to stop or redirect incomplete requests;
Sending a correction email rather than continuing with bad data and writing failures to the run history.

💬 Validation is part of automation. A flow that quietly sends bad data faster is not an improvement over a manual process.

Branching

Branching means sending a flow down different paths depending on a test. In Power Automate this is usually done with a Condition action.

Some common examples include:

If request type contains urgent;
If amount is greater than a threshold;
If requester email ends with an institutional domain;
If approval outcome equals Approve;
If a list of rows has length greater than zero.

🧭 Conditions should be readable. If the condition is hard to read, create a compose action immediately before it and test the compose output.

Branching Example

The process below treats urgent requests differently from standard requests.

A condition tests a value and sends the run down the matching branch.

🔀 Keep condition tests readable so the run history explains the decision.

Task 3: Clean, Format, and Route Request Data (15 min)

Objective: Use expressions to normalise request data, format a date, handle blank notes, and route urgent requests.

Exercise
Check

Open the flow from Task 2.
Add a Compose action called Formatted requested date and use formatDateTime() on the requested-by date.
Add a Condition using contains() so the response is true when the request text contains urgent.
In each branch, add a compose action returning either Urgent or Standard.
Test the flow twice: once with an urgent request and once with a standard request.

Use run history to confirm:

Formatted requested date produces a readable date;
Urgent and standard requests follow different branches.

Adding in Human Approvals

What Approvals Do

Approvals add a deliberate human pause.

The flow sends a request, waits for a decision, and then continues using the response.

✅ Use approvals when judgement or accountability matters.

When to Use Approvals

Approval can be incoperated into many processes, but generally are needed in cases where a business rule requires a human decision:

Requests that need authorisation;
Sign-off for documents;
Controlled access to shared resources;
Communication checks;
Exceptions to a standard process.

💬 not every flow needs approvals it is important to decide where they are appropriate.

Choose an Approval Type

Pattern	Meaning	Use when
Approve/Reject - First to respond	Any one approver can decide	A named role handles the queue
Approve/Reject - Everyone must approve	All approvers must agree	The decision needs multiple sign-offs
Custom responses	More than approve/reject	You need options such as revise, defer, or escalate
Create approval only	Approval is created but not awaited	Another flow or process manages waiting
Start and wait	The flow pauses until a response	The next step depends on the decision

⏸️ In this session we will use Start and wait for an approval because the flow needs to branch after the decision.

Give Approvers Enough Context

It is important that whomever is reviewing the process has enough infomation to make an informed decission, so before adding the action, define:

Who the approver is, what information they need, what decision options they have?
Whether comments are required?
Where the decision is recorded?
What message goes back to the requester?
What happens if the approval is not answered?

📝 An approval without enough context produces poor decisions. An approval without a record produces poor auditability.

Branch on the Approval Outcome

The approval result should drive the next step.

Approved and rejected outcomes should lead to different handling, messages, and records.

🛤️ A rejection is still a valid outcome, not a broken flow.

Handle Rejected Requests

A good rejection branch should:

Tell the requester what happened;
Include comments or next steps where appropriate;
Update the tracker;
Avoid sending confusing success messages;
Make it easy for the requester to resubmit if allowed.

🚫 Do not treat rejection as failure. A rejected request is a successful flow outcome.

Task 4: Add an Approval Step (15 min)

Objective: Add a human approval decision and handle both approved and rejected outcomes.

Exercise
Check

Open the flow from Task 3.
Add Start and wait for an approval after the request has been summarised and prioritised.
Choose Approve/Reject - First to respond. Set the title to include requester name and request type. Put the request summary, priority, requested date, and notes in the approval details.
Assign the approval to your partner, table lead, test account, or yourself.
Add a Condition based on the approval outcome: In the approved branch, send a confirmation email to yourself and in the rejected branch, send a rejection email to yourself.
Save, test, complete the approval, and inspect run history.

Use run history to confirm:

The flow paused at the approval step;
The approval went to the expected person;
The approval outcome was captured;
The correct branch ran;
Approver comments were available for later steps.

Use Excel Tables and Send Emails

From Rows to Actions

Many business processes start as a spreadsheet. Power Automate can turn each row into an action:

Read a request from a table;
Check whether it is ready;
Create a document or message;
Send a personalised email;
Update the row so it is not processed twice.

🧱 The key is structure. The workbook must behave like a small data source, not like a visual report.

Use Excel as a Tracker

Excel is often used as a lightweight tracker. Power Automate can work with Excel, but only if the workbook is structured correctly.

Note

The most important rule is that: Power Automate works with Excel tables, not arbitrary ranges of cells.*

📊 An Excel file full of values may look like data to a person, but the connector needs a named table so it can identify rows and columns reliably.

Prepare Excel Tables

When using Excel online you need to set up the document correctly:

Set up the table

Save as .xlsx
Store in OneDrive for Business or SharePoint
Format the range as a table
Name the table clearly
Use one row per record
Use simple column names

Avoid

Merged cells
Blank headers
Subtotals inside the table
Decorative layout inside the data
Very large row volumes
Editing during update-heavy runs

💡 Basically, keep it simple!

Send Emails from Table Rows

Excel mailouts work best when the table controls who is ready to receive a message.

The flow reads rows, checks status, sends only eligible messages, and updates the tracker.

📧 Always test mailouts with safe addresses before using real recipients.

Read Rows from Excel

The List rows present in a table action reads rows from an Excel table.

Important details:

Choose the correct location;
Choose the correct document library;
Select the exact workbook;
Select the named table;
Use filters carefully;
Remember that each row can be processed in an Apply to each loop.

🔎 If the table does not appear, check that the workbook is saved in the right place and the range is actually formatted as a table.

Use Flags to Track Actions

Status flags stop the same row being processed repeatedly.

After sending, update the row with a sent status and timestamp.

🧾 A tracker is only trustworthy if the flow writes back what happened.

Incorporating Word Templates

Word templates turn each Excel row into a personalised document.

Excel values populate Word content controls before the generated file is saved or sent.

📄 This pattern is useful for certificates, letters, confirmations, and notices.

Word Template Steps

A typical pattern is:

Create a Word .docx template.
Add content controls for fields such as name, course, date, and message.
Save the template in OneDrive or SharePoint.
Use Populate a Microsoft Word template in Power Automate.
Save the populated document.
Optionally convert it to PDF.
Attach it to an email or store it in a document library.

📨 If the Word template action is not available, the fallback is to build a personalised email body directly from Excel row values.

Download the Practice Files

Use the provided examples for Task 5, or create your own files with the same structure.

📁 Upload copies to your own OneDrive or SharePoint area before using them in Power Automate.

Task 5: Build an Excel-to-Email Workflow (15 min)

Objective: Use an Excel table as a small mailing tracker and send personalised test emails from table rows.

Setup
Exercise
Hints

Download Example_table.xlsx and Example_template.docx, or create your own files with the same structure.
Upload the Excel workbook to OneDrive or SharePoint.
Confirm the workbook contains a table named Table1.
Check the columns: First Name, Last Name, and Email.
Use only your own email or a test mailbox in the example rows.
Upload the Word template to the same working area if Word template actions are available in your tenant.

Use our Instant cloud flow.
Before our ••send an email•• action, add List rows present in a table, find the Excel workbook via its path, and select Table1.
Add an Apply to each action to iterate over the returned rows.
Add a Populate a Microsoft Word template and select our Word template path. Set the variables.
Move our ••send an email•• action inside the loop and add the populated template as an attachment.
Test by triggering the flow!

There is also an email column in the Excel workbook.
You could use that to dynamically set who the message is being sent to!

Review the Power Automate Workflow

What You Built

The final flow brings the main patterns together.

The workshop build reads Table1, loops through rows, fills a Word template, sends an email, and checks run history.

🏁 The same structure can be adapted to many admin workflows.

Summary of the Module

In this module you built and inspected small Power Automate flows that:

Started from a manual trigger;
Passed request details between steps;
Used connectors to send notifications;
Used expressions to clean and format values;
Routed work through conditions;
Paused for an approval decision;
Read rows from an Excel table;
Sent personalised test emails;
Updated a tracker after sending.

🧠 The technical steps matter, but the design choices matter more. A useful automation has a clear trigger, appropriate connectors, maintainable logic, a durable record, and an owner.

📚 Keypoints

Triggers define when a flow starts and what data is available.
Connectors define which systems a flow can read from or write to.
Connections define whose permissions the flow uses.
Expressions are useful for small, inspectable transformations.
Conditions should be readable and testable.
Approvals should create a decision record, not just send a notification.
Excel data must be in a named table for reliable Power Automate use.
Bulk email workflows need test mode, status fields, and duplicate-send protection.
Run history is the main debugging tool.
Production flows need ownership, documentation, and a support route.

🔦 Hints

Build manually first, then replace the trigger once the logic works.
Use one or two test records before trying a full list.
Rename actions as soon as their purpose is clear.
Prefer SharePoint lists or Dataverse when the record is important.
Keep approval messages short but complete enough to support a decision.
Use a shared mailbox when messages should come from a team rather than a person.
Add a Status column before sending repeated or bulk messages.

Further Learning

Useful next topics include SharePoint lists as workflow records, Dataverse, solution-aware flows, error handling with scopes, reminders and escalation, custom connectors, data loss prevention policies, and Power Automate Desktop.