Answered

📊 Copilot Studio + Large Excel Dataset Architecture Guidance

(2) Share

Report

Posted on by Ravi Theja Developer

Hi everyone,

I'm looking for guidance on the best architecture for a Copilot Studio agent when the data source is currently Excel.

📊 Current Situation

Excel files contain 50k–60k+ records each.

Files are stored in a SharePoint Document Library.

Users need to ask questions such as:
- Totals & aggregations
- Comparisons
- Trends
- Breakdowns
- Calculations

Data volume is expected to grow significantly over time.

🏗️ Architecture I'm Considering

User

↓

Copilot Studio

↓

Power Automate

↓

Office Script

↓

Excel (SharePoint)

↓

Result

↓

Copilot Studio

I've also explored using AI Builder / Code Interpreter by supplying the Excel file from SharePoint for analysis.

❓ My Questions

1️⃣ Is Excel + SharePoint + Power Automate a good long-term architecture for this use case?

2️⃣ Is Power Automate + Office Scripts a recommended approach for querying and calculating against 50k–60k+ rows?

3️⃣ Would a more scalable option be:

Dataverse

Azure SQL

Microsoft Fabric

Power BI Semantic Models

Something else?

4️⃣ Can Copilot Studio reliably perform aggregations and calculations on large Excel datasets, or is Excel better suited as a knowledge source only?

5️⃣ If the data volume continues to grow, what architecture would you recommend today to avoid a major redesign later?

💡 I'd love to hear from anyone who has implemented Copilot Studio with large Excel-based datasets in production and what worked (or didn't work) for you.

Thanks in advance! 🙏🚀

Categories:

Bot extensibility

General topics

All responses (4)

Answers (2)

Sort by

Verified answer

Valantis 7,545 Super User 2026 Season 2 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @Ravi Theja Developer,

Excel + Office Scripts works but it's not the right long-term architecture for 50k-60k+ rows. Office Scripts has a 5-minute execution timeout and loading 50k rows into memory for every user query will be slow and unreliable. It's fine for occasional batch operations, not for real-time user queries.

The architecture that avoids a major redesign later and handles your exact use case (aggregations, comparisons, trends, calculations) is Power BI Semantic Model as the data layer with Copilot Studio calling it. You get native DAX calculations, built-in aggregations, pre-built measures, and the Copilot Studio agent can query the model via the Power BI connector. This is the confirmed Microsoft recommended pattern for analytical queries at scale.

For data ingestion at 50k-60k+ rows from Excel, use Dataflows or Power BI Dataflows to pull from SharePoint Excel files on a schedule into the semantic model. As volume grows, move the underlying storage to Microsoft Fabric Lakehouse without changing the semantic model layer.

Excel as a knowledge source in Copilot Studio uses chunking/embedding for semantic retrieval. It's not designed for aggregations or calculations. Don't use it for analytical queries.

Short path forward: Excel in SharePoint > Power BI Dataflow > Power BI Semantic Model > Copilot Studio Power BI connector > users. This gives you aggregations, trends, and calculations natively without Office Scripts.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups  @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube
Verified answer

11manish 4,802 Super User 2026 Season 2 on at

Like
a
(2)

Report
Copy link

Link copied!

Your Excel + SharePoint + Power Automate + Office Scripts design is acceptable as a short-term solution or prototype, but I would not recommend it as the target architecture for a growing dataset of 50k–60k+ records and analytical questions.

The main issue is that Excel is being used as both the data store and query engine. The Excel Online connector has a 25 MB workbook limit, throttling, possible delays in reads/writes, and concurrency limitations. Those constraints become increasingly important as data volume and concurrent users grow.

Excel should be your source/input layer, not your long-term analytics engine. Power Automate should orchestrate, not perform large-scale data analysis. Let Fabric/SQL perform the computation, let the Semantic Model define business metrics, and let Copilot Studio provide the conversational experience.
Ravi Theja Developer 8 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @11manish , @Valantis

Thank you for the detailed explanation.

I fully agree that Power BI Semantic Models would be the preferred long-term architecture. Unfortunately, in my current environment I have some constraints:

✅ Only SharePoint and Excel connectors are currently available.

❌ Dataverse, SQL, Fabric, Semantic Models, and several other connectors are currently blocked.

❌ Some trigger options are also restricted in our environment.

My current data consists of 4 Excel files stored in SharePoint, containing approximately:

5,000 records

8,000 records

20,000 records

Additional files expected in the future

I have already evaluated a few approaches:

Knowledge Sources

I tested using the Excel files as Copilot Studio Knowledge Sources. However, this appears to be designed primarily for semantic retrieval and document Q&A rather than analytical scenarios such as:

Aggregations

Calculations

Comparisons

Trends

KPI analysis

So it doesn't seem suitable for querying the entire dataset and returning calculated results.

AI Builder / Prompt Builder + Code Interpreter

I also experimented with AI Builder Prompt Builder using Code Interpreter.

When the Excel file is supplied directly to the prompt, it can successfully:

Read the workbook

Understand user intent

Perform aggregations and calculations

Return analytical results

However, I'm unsure whether relying on prompts and passing Excel files dynamically is considered a recommended or scalable pattern for production use.

Office Scripts

I also explored Office Scripts, but maintaining script logic for every business question becomes increasingly complex as requirements grow.

My goal is to build an agent that can:

Understand user intent and conversational context

Query the complete dataset across multiple Excel files

Perform aggregations, calculations, filtering, comparisons, and trend analysis

Return the exact result requested by the user

Given these constraints (Excel + SharePoint only), what would be the recommended short-term approach for feeding Excel data into Copilot Studio for analytical queries until platforms such as Dataverse, Fabric, or Semantic Models become available?

I'd be interested in hearing how others have handled similar scenarios in production.

Thanks again for your guidance. 🙏
Valantis 7,545 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, happy we was able to help. have a nice weekend :)