[Copilot Studio] Comparing methods for giving KPI data for aggregation to an agent: chat attachment, file knowledge, and SharePoint

Introduction

Hello, I'm Kema.

To have an agent aggregate numbers, you first need to decide "how to pass the source data to the agent for aggregation."
The easiest approach is to attach an Excel file in the test chat and have it aggregate.
However, attaching a file every time is cumbersome in actual agent usage scenarios.
Ideally, the agent would have the data from the start, and you could just say "aggregate this" without any attachment.

This article covers methods for passing structured data (Excel) for aggregation to an agent, verified on actual hardware as of June 2026.
We compare three approaches—chat attachment, file knowledge, and SharePoint—focusing on whether the code interpreter can aggregate data without attachment simply by adding a file to knowledge.
This is intended as a reference for those who want to have an agent permanently hold collected data for analysis.

This article is the 5th installment in a series on building agents with Copilot Studio.
The series aims to build an agent that handles "collection → aggregation → charts → insights → documentation" end-to-end, and this article covers the entry point of "collection"—how to pass data as a prerequisite for aggregation.

Target audience: Those who want to have an agent in Copilot Studio permanently hold analytical data and aggregate it

Series Article List

1. What We're Doing This Time

We compare methods for passing aggregation-ready structured data (Excel) to an agent.

• Organize data passing into 3 routes (chat attachment, file knowledge, SharePoint)
• Test whether the code interpreter can aggregate data without attachment simply by adding a file to knowledge
• Test the official recommended approach (SharePoint + Work IQ) on actual hardware, and verify whether testing is possible without publishing

The verification uses the same fictional SaaS company data for 3 companies (CloudNova / StreamForge / Datapeak) with monthly KPI data (fictional) as used previously.

2. Why "How to Pass Data" Is a Topic

To aggregate KPIs with the code interpreter, you need to provide the target Excel to the agent.
At this point, the method determines whether "you manually pass it each time" or "it already has it from the start."

The official documentation organizes the ways a code interpreter takes in structured data into the following two:

Copilot Studio agents can use code interpreter to analyze structured files that are provided to the agent by the following two ways:

1. As an end-user, when you upload structured files while chatting with the agent.
2. As a maker, when you add SharePoint Documents library as a knowledge source, which in turn contains the structured files.

Source: Use code interpreter to analyze structured data (preview) | Microsoft Learn

In this article, in addition to "chat attachment (Route A in this article)" and "SharePoint (Route C in this article)" as described in the official documentation, we also test whether aggregation is possible with "direct file knowledge (Route B in this article)"—uploading an Excel file directly to the agent.

3. Three Routes for Passing Data

In this article, we compare the following three methods for passing aggregation-ready Excel to an agent.

Image of data retrieval flow between each component (chat, Dataverse, SharePoint) and the agent across three data routes

In this article, we verify all three routes on actual hardware.
We start with the easy Route A (chat attachment), then move to Route B (file knowledge), and finally the official recommended Route C (SharePoint + Work IQ).

4. Route A: Aggregating with Chat Attachment

Route A is the easiest method.
You attach Excel for each conversation and aggregate on the spot.
This corresponds to "end-user file upload during conversation" which the official documentation explicitly states as structured data input for the code interpreter.

As an end-user, when you upload structured files while chatting with the agent.

Source: Use code interpreter to analyze structured data (preview) | Microsoft Learn

4.1 Preparing the Verification Excel

All three routes (A, B, C) in this article use the same Excel.
To calculate year-over-year (YoY), we need data from the same period last year.
We prepare an Excel with monthly KPIs for 3 companies × (October–December for both 2024 and 2025) in a single RawMonthly sheet.
Since we're leaving aggregation to the code interpreter, this file contains only raw data—no aggregation formulas or summary sheets.

#!/usr/bin/env python3
"""Generate verification Excel for year-over-year (YoY) aggregation.

Raw data to be read by the code interpreter (CI).
3 companies × monthly KPIs for October–December of both 2024 and 2025 in a single RawMonthly sheet.
Since aggregation (totals, averages, YoY) is to be done deterministically by CI's Python (pandas),
this file contains no aggregation formulas or summary sheets (only raw data is passed).
"""
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

OUT = "kpi-raw-data-yoy.xlsx"

# Raw data (fictional). 2024 Oct–Dec has lower values before growth; 2025 Oct–Dec shows growth from prior year.
# (Company, Month, ARR (million yen), NRR (%), Operating Profit Margin (%))
RAW = [
    ("CloudNova", "2024-10", 1600, 116, 11),
    ("CloudNova", "2024-11", 1625, 117, 11.5),
    ("CloudNova", "2024-12", 1650, 118, 12),
    ("StreamForge", "2024-10", 1160, 105, -1),
    ("StreamForge", "2024-11", 1180, 106, -0.5),
    ("StreamForge", "2024-12", 1200, 107, 0),
    ("Datapeak", "2024-10", 2450, 124, 16.5),
    ("Datapeak", "2024-11", 2500, 125, 17),
    ("Datapeak", "2024-12", 2550, 126, 17.5),
    ("CloudNova", "2025-10", 1900, 119, 13),
    ("CloudNova", "2025-11", 1950, 120, 13.5),
    ("CloudNova", "2025-12", 2000, 121, 14),
    ("StreamForge", "2025-10", 1280, 108, 0.5),
    ("StreamForge", "2025-11", 1300, 109, 1),
    ("StreamForge", "2025-12", 1320, 110, 1.5),
    ("Datapeak", "2025-10", 2750, 127, 18.5),
    ("Datapeak", "2025-11", 2800, 128, 19),
    ("Datapeak", "2025-12", 2880, 129, 19.5),
]


def main() -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = "RawMonthly"
    ws.append(["Company", "Month", "ARR", "NRR", "OPM"])
    for row in RAW:
        ws.append(list(row))
    table = Table(displayName="RawMonthly", ref=f"A1:E{len(RAW) + 1}")
    table.tableStyleInfo = TableStyleInfo(
        name="TableStyleMedium2", showRowStripes=True
    )
    ws.add_table(table)
    wb.save(OUT)
    print(f"saved {OUT}")


if __name__ == "__main__":
    main()

Save this script as make_excel_yoy.py and run it with the following command (openpyxl is required).

pip install openpyxl
python make_excel_yoy.py

# Output example
saved kpi-raw-data-yoy.xlsx

The resulting RawMonthly sheet has 18 rows: 3 companies × 2 years × 3 months (Oct–Dec).

The RawMonthly sheet of the generated kpi-raw-data-yoy.xlsx. 5 columns (Company, Month, ARR, NRR, OPM) with 18 rows of raw data for 3 companies × October–December of 2024/2025

When the code interpreter aggregates this file, we determine "correctly aggregated" if the results match the following values (acceptance criteria).
Rounding is 0 decimal places for ARR and NRR, 1 decimal place for operating profit margin (OPM) and YoY.

The calculation method for each value is as follows.

• ARR/NRR/OPM Average: Simple average of the 3 months (Oct–Dec) for that year (same for both 2024 and 2025). For example, CloudNova's 2025 NRR average is (119 + 120 + 121) ÷ 3 = 120, and 2025 OPM average is (13 + 13.5 + 14) ÷ 3 = 13.5. Similarly for 2024: NRR average (116 + 117 + 118) ÷ 3 = 117, OPM average (11 + 11.5 + 12) ÷ 3 = 11.5.
• ARR YoY: (2025 ARR Average ÷ 2024 ARR Average − 1) × 100. For CloudNova: 2025 ARR average (1900 + 1950 + 2000) ÷ 3 = 1950, 2024 ARR average (1600 + 1625 + 1650) ÷ 3 = 1625, so (1950 ÷ 1625 − 1) × 100 = +20.0%.

4.2 Attaching to Chat and Requesting Aggregation

Attach kpi-raw-data-yoy.xlsx via the "Upload file" button in the test chat input, then send the aggregation request.

Please aggregate the attached Excel (RawMonthly sheet) using the code interpreter (Python/pandas). For each company (CloudNova / StreamForge / Datapeak), calculate the 2024 and 2025 averages for ARR, NRR, and OPM, and also calculate the ARR year-over-year change (YoY%). Round ARR and NRR to integers (0 decimal places), OPM to 1 decimal place, and YoY to 1 decimal place. Please calculate using Python code, not LLM inference.

In my environment, the agent loaded the attached file's RawMonthly sheet (18 rows) with the code interpreter and aggregated it using Python.
The aggregation results matched the acceptance criteria from 4.1 (CloudNova ARR YoY +20.0%, StreamForge +10.2%, Datapeak +12.4%, NRR and OPM all matched).

Full test view. kpi-raw-data-yoy.xlsx was attached in the upper right to request aggregation, the activity map on the left shows "Code (Preview)" completed (execution time 36.46 seconds), and the right shows ARR/NRR/OPM averages and YoY by company and year matching the acceptance criteria

Route A is easy but requires an attachment for each conversation.
If you want to aggregate the same data each time without attaching, Route B (file knowledge) is more suitable.

5. Route B: Holding Data in File Knowledge for Aggregation

5.1 Purpose of Verification

The official documentation supports two routes for structured data input to the code interpreter: chat attachment (Route A in this article) and connecting a SharePoint Documents library (Route C in this article).
The option of directly "uploading a file to add it to knowledge" (Route B in this article) is not included in these.

So we verify on actual hardware whether the code interpreter can aggregate data from file knowledge without chat attachment, simply by adding Excel to the file knowledge.

Note that uploaded files are stored in Dataverse, indexed, and become knowledge.

Files uploaded in Copilot Studio use Microsoft Dataverse to ingest raw files and create indexes and vector embeddings.

Source: Unstructured data as a knowledge source | Microsoft Learn

5.2 Adding Excel to File Knowledge

The Excel to use is the same kpi-raw-data-yoy.xlsx prepared in 4.1 for Route A.
From the agent's "Knowledge" page (or overview page), open "Add knowledge" and add kpi-raw-data-yoy.xlsx via file upload.

Give the knowledge a name and description.
The description serves as a hint for generative orchestration to select this knowledge, so write it in a way that makes clear what data it contains.

The description should be detailed, especially if generative AI is enabled, because it helps generative orchestration.

Source: Upload files as a knowledge source | Microsoft Learn

kpi-raw-data-yoy.xlsx added as file knowledge with indexing complete. The agent can now reference this data without an attachment

5.3 Enabling Code Interpreter

The code interpreter needs to be enabled per environment, and defaults to off.
In the agent's "Settings" → "Generative AI" under "File processing capabilities", turn on "Code interpreter".

1. In Copilot Studio, select Settings > Generative AI. Under File processing capabilities, turn on the File uploads toggle.
2. Under File processing capabilities, turn on the Code interpreter toggle.
3. Select Save.

Source: Use code interpreter to analyze structured data (preview) | Microsoft Learn

Enable "Code interpreter" under "Settings" → "Generative AI" → "File processing capabilities". In my environment, it was enabled alongside "Upload files"

5.4 Requesting Aggregation Without Attachment

In the test chat, request aggregation of the knowledge data without attaching a file.

Please aggregate the KPI Excel (RawMonthly sheet) in knowledge using the code interpreter (Python/pandas). For each company (CloudNova / StreamForge / Datapeak), calculate the 2024 and 2025 averages for ARR, NRR, and OPM, and also calculate the ARR year-over-year change (YoY%). Round ARR and NRR to integers (0 decimal places), OPM to 1 decimal place, and YoY to 1 decimal place. Please calculate using Python code, not LLM inference.

In my environment, the agent first retrieved data from knowledge, then executed Python with the code interpreter.
The aggregation results matched the acceptance criteria from 4.1 (CloudNova ARR YoY +20.0%, Datapeak +12.4%, StreamForge +10.2%, NRR and OPM all matched).

Result of requesting "aggregate the KPI in knowledge" without attachment. The agent showed "retrieve data from knowledge, aggregate with Python," and returned ARR/NRR/OPM averages and YoY by company and year matching the acceptance criteria

Without any chat attachment or SharePoint setup, the agent was able to aggregate its own data using the code interpreter.
From the perspective of having an analysis agent permanently hold data, file knowledge becomes the easiest option.

5.5 Notes on Route B (Scope of Official Support)

Route B is easy, but there are important prerequisites to understand.

First, this is not an officially documented route.
As stated in Section 2, the official documentation lists chat attachment and SharePoint Documents library as the two supported structured data inputs for the code interpreter.
The fact that file knowledge worked is a result from my environment's actual hardware testing and is not an officially guaranteed method.
For production use where reliability is required, consider Route C (SharePoint + Work IQ) in the next section.

Second, how data is passed.
In my environment, knowledge search returned the Excel content as text, and the code interpreter took that in as data within the code to aggregate.
For small tables like this one, all rows fit, but with large datasets, knowledge search may only return a portion, risking missed data.
For large data or when you need to ensure all rows are aggregated, Route C is the solid choice.

Third, file updates.
File uploads are static—updating the source file is not automatically reflected. You need to re-upload manually.

In the upload files method, files are static. So, if the file is updated, those updates aren't reflected in the uploaded version unless manually updated.

Source: Add unstructured data as a knowledge source | Microsoft Learn

6. Route C: Holding Data via SharePoint (Work IQ) for Aggregation

When data is large, you want to sync to the latest version, or you need per-user permission control, the SharePoint route that the official documentation recommends as the proper approach is suitable.

6.1 Differences from Route B

Route C (SharePoint connector) and Route B (file upload) differ in data storage location, freshness, and permission handling.

On storage and freshness:
Route C references data while it remains in SharePoint, reflecting the latest content in real time.
The official documentation compares two options for adding SharePoint as knowledge (via file upload = Option 1 / SharePoint connector = Option 2), and Route C corresponds to Option 2.

Scenario	Option 1: file upload	Option 2: SharePoint connector
Content storage	Copied into Dataverse from SharePoint	Resides in SharePoint
Content freshness	Content is synchronized every four to six hours, based on ingestion completion	Real time, and reflects the latest available content
Dataverse storage consumption	Yes, for copied files and search indexes	No

Source: Unstructured data as a knowledge source | Microsoft Learn

On the other hand, Route B in this article (local Excel file upload) is a static approach that copies and stores data in Dataverse.
Updates to the source file are not reflected until manually re-uploaded (see 5.5).

Permission verification also differs.
Via connector (Route C), the system performs a live check of access permissions using the user's credentials each time a user makes a query before responding.

When a user makes a query, the system uses their connection information to check the data source and verify they have permission to see the content. Even though the system stores chunks and indexes locally in Dataverse, it performs a live check on the queries to make sure the current user has access to the data before providing a summary or response.

Source: Unstructured data as a knowledge source | Microsoft Learn

6.2 Adding "SharePoint" to Knowledge

First, place the aggregation target Excel (same kpi-raw-data-yoy.xlsx as Route B) in a SharePoint Documents library.
Next, open the agent's "Knowledge" → "Add knowledge".

There's an important note here.
In the "Add knowledge" dialog, there are two SharePoint entry points.

• "SharePoint" inside the upper "File upload" section: A method for selecting individual files or folders on SharePoint and copying them to Dataverse. This does not support the Documents library used by the code interpreter.
• "SharePoint" in the Recommended section: Full integration via the SharePoint connector. For using structured data with the code interpreter, use this one in the Recommended section.

The SharePoint option in the file upload section is for uploading individual SharePoint files or folders to your agent. This option uploads a copy of the file from SharePoint to Dataverse and maintains a synchronous relationship to keep the file up to date.

The other SharePoint option provides the full SharePoint integration in Copilot Studio using the SharePoint connector. Use this option when you need full SharePoint connector capabilities, custom authentication configurations, or advanced query options.

Source: Unstructured data as a knowledge source | Microsoft Learn

Two SharePoint options in "Add knowledge." The upper one is inside "File upload" (copies to Dataverse, doesn't support Documents library); the lower one is the SharePoint connector in the Recommended section. Use the lower Recommended section for the code interpreter

Enter the URL of the SharePoint site (or Documents library), add a name and description, and add it.
Once the SharePoint source shows "Ready" in the knowledge list, the agent can reference this data.

SharePoint Documents library added to knowledge with status showing "Ready." The "Publish" button remains in the upper right, indicating it has not been published yet

6.3 Turning On Work IQ

Code interpreter was turned on in 5.3.
For Route C, we additionally turn on Work IQ.
The official procedure is: add SharePoint to knowledge, turn on code interpreter and Work IQ and save, then publish and test.

1. If your agent doesn't already have a SharePoint structured data file as a knowledge source, add this SharePoint file as a knowledge source.
2. In Copilot Studio, select Settings > Generative AI. Under File processing capabilities, turn on the Code interpreter toggle.
3. Under Search, select Turn on Work IQ.
4. Select Save.
5. If you added a SharePoint file following instructions in the first step, publish your agent.
6. Test your agent by giving it a query that requires the agent to do a computation in order to answer.

Source: Use code interpreter to analyze structured data (preview) | Microsoft Learn

In practice, under "Settings" → "Generative AI" in "Search", turn on "Tenant graph grounding with semantic search" and save.
The official "Turn on Work IQ" corresponds to "Tenant graph grounding with semantic search" in the Japanese UI.

Enable code interpreter under "File processing capabilities" and enable Tenant graph grounding with semantic search (= Work IQ) under "Search"

6.4 Testing Without Publishing

The official procedure includes "publish your agent" as Step 5.
However, this is one step in the procedure, not a statement that "testing is impossible without publishing."
The test panel reflects the saved draft, not the published version.

The Test agent pane automatically refreshes when you select Save after editing your topics.

Source: Quickstart guide for building agents with generative AI | Microsoft Learn

So, without publishing (without pressing the "Publish" button in the upper right), I requested the same aggregation as Route B in the test panel.

Please aggregate the KPI Excel (RawMonthly sheet) from the SharePoint knowledge using the code interpreter (Python/pandas). For each company (CloudNova / StreamForge / Datapeak), calculate the 2024 and 2025 averages for ARR, NRR, and OPM, and also calculate the ARR year-over-year change (YoY%). Round ARR and NRR to integers (0 decimal places), OPM to 1 decimal place, and YoY to 1 decimal place. Please calculate using Python code, not LLM inference.

Even without publishing, the agent retrieved data from the SharePoint knowledge and then executed Python with the code interpreter.
The aggregation results matched the acceptance criteria from 4.1 (CloudNova ARR YoY +20.0%, Datapeak +12.4%, StreamForge +10.2%, NRR and OPM all matched).

Result of requesting aggregation from the test panel without publishing. The trace on the left shows "Search resources (knowledge retrieval)" → "Code (code interpreter execution)," confirming the Excel was retrieved from SharePoint and aggregated with Python, returning values matching the acceptance criteria

7. Having the Analysis Sub Hold Data in Multi-Agent

The end goal of an end-to-end agent envisions a configuration with a parent agent serving as the entry point and sub-agents for each process.
In this case, how to have the "analysis sub" responsible for aggregation hold data becomes a key question.

What comes into play here is the official specification that when a parent agent calls a child agent, only the conversation history is automatically carried over.

Copilot Studio passes along the conversation history by default when one agent calls another, so the connected agent knows what's already been discussed.

Source: Explore multi-agent orchestration patterns | Microsoft Learn

Therefore, you cannot design with the assumption that files attached to the parent are automatically passed as-is to the analysis sub (passing additional data is not verified in this article).
For the analysis sub to aggregate, the sub itself needs to hold the data.
As confirmed in this article, if you have the sub hold data via file knowledge (Route B) or SharePoint (Route C), the sub can aggregate its own data when delegated from the parent.

8. Using the 3 Routes Appropriately

After verifying on an actual environment, the ways to pass data can be organized as follows.

• For one-off analysis, use Route A (chat attachment). Since data is passed with each conversation, no permanent configuration is needed.
• If you want to aggregate the same data every time without attachments, use Route B (file knowledge). This is the easiest approach, and for small data, code interpreter was able to aggregate without attachments. However, it is not an officially guaranteed route, and there is a risk of missing data with larger datasets.
• If you need large data, synchronization with the latest version, or access control, use Route C (SharePoint + Work IQ). This is the method officially presented as the proper approach and is suited for production use. Publishing is required for deployment to production channels, but operation could be verified in the test panel without publishing.

The practical solution for giving data to an analysis sub-agent comes down to choosing between B and C based on data size and reliability.

Summary

We compared the "ways to pass data" that precede aggregation across 3 routes.

• The official inputs for code interpreter are two: chat attachments and SharePoint Documents
• Even with file knowledge (Route B), code interpreter was able to aggregate without attachments (small data, not officially guaranteed)
• For large data or when synchronization and permissions are required, SharePoint + Work IQ (Route C) is the proper approach. In my environment, aggregation was possible in the test panel even without publishing
• When a parent agent calls a child agent, what is automatically carried over is the conversation history. It is safer to have the child agent responsible for aggregation hold the data in its own knowledge in advance (automatic handover of attachments was not verified in this article)

References

• Use code interpreter to analyze structured data (preview) | Microsoft Learn
• Upload files as a knowledge source | Microsoft Learn
• Add unstructured data as a knowledge source | Microsoft Learn
• Unstructured data as a knowledge source | Microsoft Learn
• Explore multi-agent orchestration patterns | Microsoft Learn
• FAQ for Copilot Studio billing and licensing | Microsoft Learn