Introduction
One of the first large-scale agent projects I worked on was an HR triage bot. The kind of thing that sounds simple when someone describes the problem: staff send questions to an HR inbox, someone reads them, finds the right policy, and replies. Multiply that by 100,000-150,000 emails a year and you start to understand why the customer wanted help.
The goal was straightforward - build an AI agent in Copilot Studio that could handle the bulk of these queries by grounding its answers in the organisation's HR policies and internal documentation. On paper, a textbook use case for a knowledge-grounded agent. In practice, it became one of the most educational builds I've done, mostly because I had to solve problems that Microsoft would quietly make disappear a few months later.
When the only door is the wrong door
At the time, Copilot Studio's knowledge connector only supported the built-in Microsoft Graph interface for searching content. For a lot of use cases, that's perfectly adequate. But for this project - where the agent needed to reason across a broad, overlapping set of HR policies, procedures, and guidelines - it wasn't enough. The content was dense, the topics were interconnected, and the native search just couldn't surface the right material with the precision the agent needed.
So I did what any reasonable person would do. I started experimenting.
My first attempt was uploading the full policy documents directly into Dataverse tables, hoping to lean on the semantic indexing capabilities built into the platform. I'll be honest - this was a shot in the dark. The kind of test where you know the odds aren't great, but you want to rule it out before committing to something more complex. It didn't take long to rule it out. The content was too broad, the documents too large, and the indexing wasn't designed to handle that shape of data in a way that would give the agent meaningful retrieval.
Building the pipeline nobody asked for
With the simpler options exhausted, I went deep. I built a custom ingestion pipeline in Power Automate that would take the source documents and transform them into something the agent could actually work with.
The pipeline did a lot of heavy lifting. It would extract the raw text from each document, chunk it into meaningful segments, generate overlap buffers on either side of each chunk so the agent wouldn't lose context at the boundaries, and store references to the preceding and following chunks so retrieval could be stitched back together intelligently. On top of that, it extracted additional metadata - keywords, topic labels, category tags - to give the search layer more to work with when matching a user's question to the right content.
All of this got ingested into Dataverse, where the agent could query it.
And here's the thing - it actually worked. The retrieval quality was genuinely good. The chunking strategy, the buffer overlaps, the metadata enrichment - all of it contributed to the agent surfacing relevant, contextual answers from a large and complex content set.
The catch that kills it for chat
The problem wasn't quality. It was speed.
The search method I had to use against Dataverse - given the limitations of what was available as a connector at the time - was slow. Not "a bit sluggish" slow. "Regularly timing out" slow. For a batch process or a back-office tool, that might be tolerable. For an AI agent sitting behind a chat interface, where someone is waiting for a response in real time, it was a dealbreaker.
What I really wanted was to use the Dataverse knowledge source as a native connector inside Copilot Studio, so the agent could reason over the indexed content directly — pulling from the full depth of what I'd built without routing through a clunky search-and-return pattern. But that option simply didn't exist yet.
There's an important nuance here worth calling out. With the custom Dataverse approach, I had full control over how search was performed. I could shape the query logic, weight different metadata fields, and map user intent into a precise search query rather than relying on a generic keyword match. That's genuinely powerful — especially for domains like HR where the same question can be phrased dozens of different ways, and the difference between the right policy and a similar-but-wrong one matters.
But that level of control comes with real overhead. You're maintaining custom query logic, tuning search relevance, managing the chunking strategy, and debugging retrieval failures - all inside a system that isn't really designed for that depth of customisation in a live chat scenario. It's the kind of architecture that makes sense when you need it, but it's a lot to carry when the alternative is about to get dramatically simpler.
Technical breakdown
The flow triggers whenever a document lands in the HR Policies SharePoint library. From there, it does two things: extract metadata, and chunk the content for downstream indexing.
The metadata step uses AI Builder custom prompts - two separate calls, one to pull a clean document name, another to generate tags. Both feed off the raw document content and store the results as variables. It's a straightforward pattern - let the LLM do the classification work that would otherwise need a brittle set of rules or manual tagging.
The chunking step is where the flow earns its complexity. It calculates an overlap stride up front - how many characters each chunk should share with its neighbours - then enters a Do Until loop that walks through the document content, slicing it into overlapping windows. Each iteration calculates the remaining text, takes a chunk, wraps it with prefix and suffix context from the surrounding content, and appends it to an array. The loop tracks its own position and exits once the full document has been processed.
The overlap matters. Without it, you get hard cuts at arbitrary character boundaries - mid-sentence, mid-paragraph - and any search or retrieval that lands near a chunk boundary loses context. The prefix and suffix padding gives each chunk enough surrounding material to remain coherent on its own.
By the end of the flow, you have a set of enriched, overlapping chunks tagged with the document's name and classification - ready to push into a search index, a Dataverse table, or whatever retrieval layer sits downstream.
The feature that made it all feel silly
A few months later, my Microsoft contact mentioned something was in the pipeline. Shortly after, we got access to the Dataverse and Azure AI Search-backed ingestion of SharePoint document libraries as a native knowledge source in Copilot Studio.
It was, frankly, glorious.
All the painful work I'd done manually - the text extraction, the chunking, the metadata enrichment, the ingestion pipeline - Microsoft had wrapped it up into a managed capability. Point it at a SharePoint document library, let it index, and the agent could reason over the content natively. The thing I'd spent weeks building was now a configuration step.
I won't pretend I wasn't a little conflicted. There's a strange mix of validation and mild frustration when a platform catches up to exactly the problem you've been solving by hand. But mostly, it was just good. The kind of feature that genuinely moves the needle for people who want to build knowledge-grounded agents without needing to understand chunking strategies and vector search pipelines.
The custom route still has its place
I don't want to overstate the "Microsoft solved everything" angle. If you have specific requirements around how content is extracted, how chunks are sized, how metadata is generated, or how search relevance is tuned, spinning up your own Azure AI Search instance and building a custom pipeline is still the right call. You get full control over the extraction rules, the processing logic, and the search configuration. For complex, high-stakes use cases, that control matters.
The trade-off is maintenance. You own the pipeline, you own the index, you own the debugging when something doesn't retrieve correctly. For a lot of organisations — especially those early in their AI journey - that's a significant ask.
What the native integration does brilliantly is collapse the distance between "we have documents in SharePoint" and "we have a working knowledge-grounded agent." That's not a small thing. For the majority of use cases, it's exactly the right level of abstraction.
Early innings, long memory
This whole experience sits in a specific moment in time - the early days of Copilot Studio's knowledge capabilities, when the tooling was evolving faster than anyone could document it. The features I wrestled without exist natively now. The workarounds I built are largely unnecessary.
But I'm glad I built them. The custom pipeline taught me how semantic search actually works at a practical level - not the theory, but the messy reality of chunking documents, managing context windows, and mapping human questions to machine-readable queries. That understanding doesn't become less valuable just because the platform abstracts it away. If anything, it makes me better at knowing when the abstraction is enough and when it isn't.
The best features in any platform are the ones that take a genuinely hard problem and make it accessible without hiding what's happening underneath. Microsoft got that right with this one. And for the teams out there still building custom pipelines because the native option doesn't quite fit - I see you. It's worth the effort. Just don't be surprised when the platform catches up.