Wiring Copilot into a real data source to build a finance report that isn't a lie

Background

There are two completely different things people mean by "get Copilot to build me a finance report." The first is typing "summarise this quarter's spend" into a chat window and hoping the model somehow knows what quarter, what spend, and what "this" refers to. The second is pointing it at an actual named dataset - a Power BI model, a Dataverse table, a defined range in an Excel workbook - and asking a specific question against it.

The first produces something that reads confidently and means nothing. The second produces a number you can trace back to a source row. If it's going anywhere near a real financial decision, only the second one is worth building.

Setup

The build sequence

The steps have to happen in this order - each one is what makes the next one trustworthy rather than just plausible:

1. Pick one dataset, not a folder of loose spreadsheets. "Finance" as a source is not a source - it's a filing cabinet. A Power BI semantic model or a single Excel workbook with named ranges for each measure gives Copilot (and you) something with an actual schema, so a question about "spend by category" resolves to one column, not a guess across several files that might disagree with each other.
2. Connect it through a scoped connector, the same rule as the human-in-the-loop guide - a connection account that can read the finance dataset and nothing wider. "Can read Finance" is not a permission if the account behind it also has access to HR or payroll data it was never meant to touch.
3. Write the report as a specification, not a prompt. Detail below.
4. Test it against a number you already know the answer to. Before trusting it with a live period, ask it about a closed month you can check by hand.
5. Automate the recurring version only after the manual one's been checked, with a human review step still in the loop.

Writing the spec, not the prompt

The difference between a spec and a mood is specificity someone else could check without re-running it:

Vague (a mood, not a spec):
"Tell me how we're doing on spend."

Specific (a spec):
"Using [NAMED DATASET], show spend by category for [THIS QUARTER] versus
[LAST QUARTER], as a table with a percentage variance column. Flag any
category where variance exceeds 15% in either direction. Round to the
nearest £100. If a category has no data for one of the two periods, say so
explicitly rather than showing it as zero."

The more specific the ask, the less room the model has to fill a gap with something invented. The "say so explicitly" line matters more than it looks - a missing period silently rendered as zero reads identically to a genuine zero-spend month, and only one of those is true.

Testing before you trust it

Don't test the report on the live period you actually care about - test it on one you can already check by hand:

• Pick a closed month with a total you already know. Ask the exact question you'll automate later, against that closed period.
• Check the number against the source directly, not against your memory of roughly what it should be. "About right" isn't a pass.
• If it's wrong, the fault is almost always the connector or the schema, not the phrasing of the question. A grounded query against the right table doesn't get arithmetic wrong; a query silently grounded against the wrong table, or a stale cached copy of the dataset, does. Fix that before the report ever touches a live period.
• Re-run the same test after any change to the underlying dataset's structure - a renamed column, an added category, a changed measure definition. A report that was grounded correctly last month can silently break this month if nobody re-checks it against the new schema.

Automating the recurring version, without skipping the review

Once the manual version has passed the test above, a Power Automate flow can trigger the same grounded question on a schedule - the first working day of the month is the usual choice - and post the result somewhere a person still looks at before it goes further:

• Post to a channel someone actually reviews, not an inbox that gets archived unread. A Teams channel with a named owner who reads and reacts, not a distribution list.
• Keep the automated run identical to the tested manual run. Any "while we're at it" enhancement to the automated version - a new column, a different rounding rule - needs its own test against a known month before it goes live, the same as the original build did.
• Log the run, not just the output. Which dataset version it queried, when, and what came back. If the underlying numbers get restated later (a common finance event - a correction, a late invoice booked back to the period), you need to know which report ran against which version of the truth.