Build Real #2 — Create the Copilot Studio Agent and Power Automate Flow: Step by Step

Architecture — Why We Use OneDrive-First

You may have read tutorials that pass the file directly from Copilot Studio to Power Automate via Power Fx formulas like Topic.ExamFile.Content. In practice this causes type mismatch errors on many Microsoft 365 tenants because Copilot Studio stores uploaded files as Blob objects which are not compatible with Agent Flow text inputs.

This guide uses the OneDrive-first pattern — the approach that works reliably in production:

WHAT WE BUILD:

Student uploads file
        ↓
Copilot Studio saves file automatically to OneDrive
(this happens inside the Question node — no extra step needed)
        ↓
Copilot Studio calls Power Automate flow
passing only: the filename (text) — no file binary
        ↓
Power Automate reads the file FROM OneDrive directly
using the filename it received
        ↓
Power Automate reads each question → AI answers → saves to SharePoint
    

No type mismatch. No base64 conversion errors. No Power Fx dot notation on Blob objects. The file stays in OneDrive — both tools access it there.

Phase 2 — Build the Power Automate Agent Flow

Step 7: Create the Agent Flow

1. In the Copilot Studio left sidebar, click Flows (the icon looks like a lightning bolt)
2. Click "+ New flow"
3. A small dropdown appears with exactly two options:
   • Agent flow
   • Cloud flow
4. Click Agent flow
5. The Power Automate designer opens in a new browser tab. You see the flow canvas with two nodes already connected by an arrow:
   • Top blue node: "When an agent calls the flow" — this is the trigger. Do not delete it.
   • Bottom blue node: "Respond to the agent" — this sends data back to Copilot Studio. Do not delete it.
   All the steps you add go between these two nodes.
6. At the top of the page, click the flow name showing "Untitled" → type Process Exam Questions → click outside to save the name

Step 8: Add inputs to the trigger

1. Click the "When an agent calls the flow" trigger node to expand it
2. Inside the expanded node, scroll to the bottom. You see a grey button: "+ Add an input". Click it.
3. A row appears with:
   • Left: a type selector showing a dropdown
   • Right: a name field with placeholder "Input"
4. Click the type dropdown → select Text
5. Click the name field → clear it → type FileName
6. Click the description field that appears below → type: Name of the uploaded Excel file

Step 9: Read the Excel file from OneDrive

1. Click the + (plus) button between the trigger node and the "Respond to the agent" node. A search box appears.
2. Type: List rows present in a table
3. In the results, find the green Excel icon labelled Excel Online (Business) → click "List rows present in a table" under it
4. The action card expands showing these fields:

Location field:

1. Click the Location dropdown → select OneDrive for Business

Document Library field:

1. Click the Document Library dropdown → select OneDrive

File field:

1. Click the folder icon on the right side of the File field
2. A file browser opens showing your OneDrive for Business folders
3. Navigate to the ExamProcessing folder
4. Click your exam-questions.xlsx file → click Select
5. The file path appears in the field

Table field:

1. Click the Table dropdown
2. Power Automate connects to the file and reads available tables
3. You should see ExamQuestions — click it
4. If the dropdown is empty or shows an error:
   • Verify the file is saved to OneDrive for Business (not local)
   • Open the file → confirm the data has Insert → Table applied
   • Confirm the table name is exactly ExamQuestions (Table Design tab → Table Name field in Excel ribbon)

Step 10: Initialize a variable to collect answers

1. Click the + below the List rows action
2. Search: Initialize variable
3. Click Variables → Initialize variable
4. Fill in:
   • Name: type AllAnswers
   • Type: click dropdown → select String
   • Value: leave this field completely empty

Step 11: Add the loop — Apply to each

1. Click + below Initialize variable
2. In the search box type: Apply to each
3. Under Control category, click "Apply to each"
4. A loop container appears with one field: "Select an output from previous steps"
5. Click inside this field
6. A dynamic content panel opens on the right (or slides up from the bottom depending on screen size)
7. Look for the section labelled "List rows present in a table"
8. Under that section, click value (this is the array of rows from your Excel table)
9. A blue pill labelled value appears in the field

Step 12: Add AI Builder inside the loop

1. Click the + button inside the Apply to each loop. Look for the + sign inside the loop boundary, not the + below the loop.
2. Search: Run a prompt
3. Under AI Builder connector, click "Run a prompt"
4. The action card appears inside the loop with a Prompt field
5. Click "+ Create a prompt" (you may also see this as "New prompt")
6. A Prompt Builder panel opens on the right side of your screen. This is part of Power Automate — not a separate website or portal.

Inside Prompt Builder — follow these exact steps:

1. At the top, click the name field showing "Untitled" → type Answer Exam Question
2. In the large white text area (the prompt body), click inside it and type exactly:

   You are an expert teacher and subject matter expert.
   A student has submitted an exam question for evaluation.
   Provide a clear, accurate, and educational answer.
   
   Question: [Question]
   
   Instructions:
   - Answer accurately and completely
   - Keep answers to 2-5 sentences
   - Use plain English suitable for a student

3. Notice [Question] in the text above. Now create the input variable that will replace it:
   • Below the text area, click "+ Add input"
   • A row appears with a type selector and name field
   • Leave type as Text
   • In the name field, type Question
4. After typing the name, look at the prompt text above — [Question] should now be highlighted or linked showing it is connected to your input
5. Click Save (blue button, usually at top right of the Prompt Builder panel)
6. You return to the Power Automate flow canvas. The "Run a prompt" action now shows Answer Exam Question as the selected prompt.
7. Below the prompt selection, you see a field labelled Question
8. Click inside the Question field
9. Dynamic content panel opens → look for the section "Apply to each" or "List rows present in a table" → click Question (this is the Question column from your Excel row)
10. A blue pill labelled Question appears in the field

Step 13: Append each answer to the variable

1. Click + inside the loop, below "Run a prompt"
2. Search: Append to string variable
3. Click Variables → Append to string variable
4. Fill in the Name field: click the dropdown → select AllAnswers
5. Now fill in the Value field. You will mix typed text with dynamic content — follow these steps one by one:
   • Click inside the Value field
   • Type: Question: (include the space after colon)
   • Click "Add dynamic content" (a lightning bolt icon or link below the field) → under Apply to each / current item → click Question
   • Press Enter (new line)
   • Type: Answer:
   • Click "Add dynamic content" → under "Run a prompt" section → click text (this is the AI's response — it may appear as "text" or "Prediction text" depending on your environment)
   • Press Enter twice (two new lines for spacing)
   • Type: --- (three dashes as a separator between Q&A pairs)
   • Press Enter
6. The completed Value field should contain:

   Question: [Question]
   Answer: [text]
   
   ---
           

   Where [Question] and [text] are blue dynamic content pills.

Step 14: Return the result to Copilot Studio

1. Click the "Respond to the agent" node at the very bottom of the flow. This node was pre-created — you do not need to add it.
2. Inside the node, click "+ Add an output"
3. Type selector: click dropdown → select Text
4. Name field: type ProcessingResult
5. Value field: click inside it → dynamic content panel opens → click AllAnswers
6. Click Save draft (top right)
7. Click Publish (blue button, top right)
8. Wait for the confirmation message: "Your flow is ready to use" or "Flow published successfully"

Phase 4 — Test

Step 19: Test in the Copilot Studio test panel

1. Click Test (top right of Copilot Studio, looks like a chat bubble with a play icon)
2. A test chat panel slides in from the right side of the screen
3. Type: evaluate exam → press Enter
4. The agent responds with your welcome message
5. Click the paperclip (attachment) icon in the test chat input bar
6. Select your exam-questions.xlsx file → click Open
7. Press Enter or click Send
8. You see a loading indicator — the flow is running. Wait 30-90 seconds.
9. The agent responds with your confirmation message when done

How to diagnose if the flow fails

1. Go to powerautomate.microsoft.com
2. Click My flows in the left sidebar
3. Click Process Exam Questions
4. Scroll down to 28 day run history
5. Click the most recent run (red circle with X = failed, green circle = success)
6. The run detail page shows every step. Failed steps have a red X. Click a failed step to expand it — the error message tells you exactly what went wrong.