AI Tools Academy
Power Automate 0/18

Phase 5 · Power Automate · Level 2 · Practitioner

Build it yourself: an expense-logging pipeline

Hands On · 16 minLast checked against the live product: 13 July 2026

30-second recall from earlier lessons
You anchor a Gem to your Fernway sales sheet, and it reports the South region as weakest. But the sheet has rows misspelled 'Sotuh'. What's the risk?
A Deep Research report comes back with clear sections, a confident conclusion and a list of citations. You need one of its figures for a client proposal. What should you do?

By the end, you'll be able to…

  • Build a four-step flow joining Forms, Excel, Teams and Outlook end to end
  • Use Get response details, an Excel table and a formatted-date expression correctly
  • Test the whole pipeline, verify the real result, and prove it with a checklist

Why it matters

This is where Level 2 comes together. You've met the everyday connectors, expressions, Copilot and run history one at a time; now you build a single flow that uses several of them for a job Fernway actually needs. By the end you'll have a working, tested pipeline that turns a submitted form into a logged row, a team notification and a confirmation email, the shape most real workplace automations take.

Before you start

You'll need a work or school Microsoft account signed in to make.powerautomate.com, with access to Microsoft Forms, Excel (in OneDrive or SharePoint), Teams and Outlook on that same account. Set aside about twenty minutes. Nothing here touches anyone else's data (you'll submit your own test form, log to your own spreadsheet, post to a channel you choose, and email yourself), so it's a safe sandbox.

A privacy note from Phase 0: use invented details for every test submission. Don't practise with real expense data or anyone's personal information. If you'd like a ready-made scenario, borrow Fernway's cast: Grace in Finance submits a claim, and it needs logging and confirming.

The goal, in one line:

When an expense-claim form is submitted, log it to a shared spreadsheet, tell the Finance team, and email the claimant a confirmation.

That's four everyday apps in one flow: Forms (the trigger), Excel (the log), Teams (the notification) and Outlook (the confirmation). We'll build it one tested piece at a time, the habit that never lets you down.

Step 1: Prepare the form and the table first

A flow can only be as good as what it writes into. Before building anything, set up the two things the flow depends on.

The form. In Microsoft Forms, create a short Expense Claim form with four questions: Name, Amount, Category, and Description. Keep it simple; this is the front door.

The Excel table. In Excel (saved to OneDrive or SharePoint), create a workbook (call it Expenses-Log.xlsx) with a sheet whose columns are Date, Name, Amount, Category, Description. Now the crucial step from Lesson 1: select those columns and use Insert > Table to format them as a real table. A sheet that merely looks tabular won't work; the Excel actions need a proper named table. Give it a moment now and you save yourself the commonest failure later.

Step 2: Build the trigger and fetch the answers

In the maker portal, go to Create and start an automated cloud flow. Name it clearly straight away: "Log expense claims".

Add the trigger: the Microsoft Forms trigger When a new response is submitted, and select your Expense Claim form. If prompted, sign in to create the connection.

Now the step new builders always forget. Add the Forms action Get response details, point it at the same form, and pass it the response ID from the trigger. Remember why: the trigger hands you only an ID, not the answers. This step turns that ID into the actual Name, Amount, Category and Description as dynamic content you can use downstream. Skip it and every logged row comes out blank.

Step 3: Log the claim to the Excel table

Below the details step, add the Excel Online (Business) action Add a row into a table. Point it at the location: the Expenses-Log.xlsx file, the worksheet, and the table by name. Once it can see the table, it shows a field per column. Fill them:

  • Date: don't leave this to the form. Use an expression so every row is stamped with when it was logged: formatDateTime(utcNow(), 'dd/MM/yyyy') gives a tidy UK date. (Capital MM; lowercase would be minutes.)
  • Name, Amount, Category, Description: pick the matching dynamic-content values from Get response details.

Save, and test just this much before adding more: submit the form once and confirm a correctly filled row appears. Getting the hardest part (the table and the date) working on its own means that if a later step misbehaves, you know exactly where to look.

The logging half, described for CopilotPower Automate
When someone submits the "Expense Claim" form, get the full response details, then add a row to the "Expenses" table in Expenses-Log.xlsx, setting Date to today's date formatted as dd/MM/yyyy and filling Name, Amount, Category and Description from the response.

Why this works: If you'd rather have Copilot draft this and check its work, this names the trigger, the get-details step, the table and the date expression. Because you've built it by hand, you can verify Copilot included the 'get details' step and the date, the exact things it tends to miss.

Step 4: Notify the Finance team in Teams

Add the Teams action Post message in a chat or channel. Choose to post to a team and channel you have (a test channel is perfect) and write a message that pulls in the live details, for example "New expense claim logged: " then the Name value, then " £" and the Amount value. This is the low-risk notification step, so it's a comfortable one to add and test.

Save and test again: submit another claim and check the row and the Teams message both appear. Two steps working together, tested, before you add the third.

The Teams notification stepPower Automate
After the row is added, post a message to the "Finance" team's "Claims" channel saying "New expense claim logged:" followed by the claimant's name and the amount, so the team sees it without opening the spreadsheet.

Why this works: This names the destination (a specific team and channel) and the exact message with placeholders for the live values, which is all the 'Post message in a chat or channel' action needs. Testing after adding it, not at the very end, is the small-tested-steps habit that keeps failures easy to locate.

Step 5: Email the claimant a confirmation

Finally, add the Outlook action Send an email. For your test, set the recipient to yourself (in a real flow you'd use the claimant's address, if the form captured it). Write a short confirmation: a subject like "Expense claim received", and a body that thanks them and repeats the key details: "We've logged your claim for £" then the Amount, " under " then the Category. Repeating the details back is a small kindness that also doubles as a receipt.

The confirmation email stepPower Automate
After posting to Teams, send an Outlook email to me with the subject "Expense claim received" and a body that confirms we've logged the claim, repeating the amount and category from the response.

Why this works: This names the action (send an email), the recipient, and a body that echoes the claim details back, a clear, verifiable spec. Sending only to yourself during testing keeps the build safe, exactly the sandbox rule from the hands-on: no message goes to anyone else until you've proven it works.

Test the whole pipeline

Now run the full thing. Click Save, then Test, choose manually, and start the test. Submit your Expense Claim form with invented details and wait up to a minute or two.

Watch the run: every step should show a green tick. Then, and this is the part that actually proves it, check all three real results:

  1. Open Expenses-Log.xlsx and confirm a new row with today's date and the right values.
  2. Open the Teams channel and confirm the message posted, with the live name and amount.
  3. Check your inbox for the confirmation email with the details echoed back.

Green ticks mean it ran; the row, the message and the email mean it worked. Only the second one counts.

Your checklist

Every item should be a yes:

  • The form exists and has Name, Amount, Category and Description.
  • The Excel sheet was formatted as a real table via Insert > Table.
  • The flow has a clear name.
  • The trigger is When a new response is submitted, and Get response details comes straight after it.
  • Add a row into a table points at the right file, worksheet and table, with Date set by a formatDateTime(utcNow(), 'dd/MM/yyyy') expression and the other columns from the response.
  • The Teams step posts to a channel you chose, with live details in the message.
  • The Outlook step emails you (during testing) with the details echoed.
  • You clicked Save and ran a Test with a real submission.
  • The row, the Teams message and the email all actually appeared; you checked, not assumed.

If it didn't work

  • Blank values in the row. You almost certainly skipped Get response details, or mapped the columns from the trigger instead of from that step. Fetch the details and map from there.
  • "The table couldn't be found" on the Excel step. The sheet wasn't formatted as a table, or you pointed at the wrong file/worksheet. Re-check Insert > Table and the location.
  • The date column shows minutes, or looks wrong. Lowercase mm gives minutes; use capital MM for the month in formatDateTime.
  • A red cross on the Teams or Outlook step. Usually a connection needs signing in, or you picked a channel you no longer have access to. Open the failed step in run history, read the error, and fix the one thing, the routine from Lesson 5.

Self-check

Answer honestly:

  • Can you point to the four connectors in your flow and say which is the trigger and which are actions?
  • Do you know why Get response details is essential, and what breaks without it?
  • Could you now add a fifth step (say, only emailing the claimant when the amount is over £100) and predict how you'd test it?

Yes to all three means you don't just have a working pipeline; you understand how it's put together and how to grow it.

Try it now

Common mistakes

  • Building all four steps before testing any. A four-step flow that fails is four places to hunt. Test after step 3, again after step 4, again after step 5; small tested stages always beat one big hopeful build.
  • Forgetting the table and the get-details step. These are the two structural must-haves: a real Excel table and Get response details after the Forms trigger. Miss either and the flow runs but logs nothing useful.
  • Emailing a real person during testing. Send the confirmation to yourself until the flow is proven. Only point it at a real claimant once you've watched it work; sending is one of the actions you don't undo.
  • Declaring it done at the green ticks. The ticks say it ran; the row, the message and the email say it worked. A mis-mapped field passes every tick and still logs the amount in the category column. Open the actual results and read them. The flow does exactly what you wired, at speed, with no instinct for a value in the wrong place. What it produces still has your name on it.

Keeping current

The connectors and the build-test-verify routine here are durable; Microsoft moves the odd action name and button. Microsoft's Create a cloud flow guide and the Excel Online (Business) connector reference on Microsoft Learn track the current screens. Accurate as of 13 July 2026.