Copilot in Excel and Power BI: the table it builds never refreshes

What does Copilot in Excel actually do with a Power BI report?

Copilot in Excel is the chat pane sitting down the right hand side of your workbook. The Power BI part of it is newer. This feature lets you give Copilot a whole Power BI report as the source for your question, so you do not have to type the numbers into a sheet first or export a CSV and clean it up.

A Power BI report sits on top of a semantic model. Microsoft uses this term to describe the tables, the relationships and the calculations behind the charts. People used to call it a dataset, and it is the part that holds the real numbers. A report is just the pictures drawn on top of those numbers.

Microsoft’s help page for Copilot in Excel with Power BI says Copilot can answer questions about the data, create summaries for quick review, or insert editable tables, charts and other Excel elements directly into your workbook. The important word in that list is editable. Copilot drops an ordinary Excel object into your sheet for you to change, and it does not give you a window onto Power BI.

Finding this tool takes a short walk through the menus. You select the plus symbol in the prompt field inside the Copilot pane to open the Add work content menu. You pick More from the row of tabs, then Power BI, then choose a report you have permission to open, and then you type your prompt. This is the exact same idea as Copilot inside Power BI itself, where it also has a list of things it will not answer, just pointed the other way round.

Does the data refresh after Copilot puts it in your sheet?

Microsoft states very plainly that there is no connection. The help page reads: “When you attach a Power BI report to a Copilot in Excel request, Copilot imports a one-time snapshot of the data. The result does not stay connected for automatic refresh.”

A snapshot means a copy taken at one single moment. The numbers landing in your sheet show whatever the model held at the second you pressed send, and then they stop updating. Power BI can refresh in the background every morning for a month, but that Excel table will not change a single cell.

Microsoft offers only one manual way to get new numbers. You have to attach the report again and submit a new prompt to reflect newer Power BI data. Copilot pasted the table without anything behind it, so there is no refresh button to press. You just have to do the whole thing over every time you want an update.

This works fine for a question you only ask once. Someone might ask which five accounts carried last quarter, so you ask Copilot, get a table, and paste it into an email. You do not need those numbers to stay alive and update.

Things go wrong the moment the sheet gets saved with a name and reopened. A file called Q3 margins by region in a shared folder looks exactly like a live report. Nobody opening it can tell that it stopped updating in September. Excel will not warn them, because Excel thinks someone just typed those numbers in.

I think it is worth running the same quick checks I use for telling whether ChatGPT or Copilot got an Excel number right before you send anything built this way to your team.

How do you get Power BI data into Excel that does refresh?

Power BI has offered refreshable Excel output for years. This sits under a different menu in a different product, so that is probably why the Copilot write-ups miss it. There are three main ways to do this, and Microsoft documents all of them on one page about creating Excel workbooks with refreshable Power BI data.

The first method is Analyze in Excel. You open a report in the Power BI service, select Export, select Analyze in Excel, and Power BI builds you a workbook connected to the whole semantic model. You then work in PivotTables using the real fields from the model. This stays connected, so refreshing the workbook will pull the current numbers.

The second option is Export with live connection, and this one is narrower on purpose. You start at one visual, open its More options menu, pick Export data, choose the Summarized data card, and then select the file format called “.xlsx (Excel) with live connection”. Microsoft limits this to 500,000 rows. This gives you an ordinary Excel table that refreshes its data against Power BI.

The third way uses the Power BI add-in inside Excel. This helps when you start from an existing workbook instead of the Power BI website. You open the PivotTable dropdown on the Insert ribbon and choose From Power BI (Microsoft), or you go to the Data ribbon, click Get Data, click From Power Platform, and click From Power BI (Microsoft). A pane opens where you can insert a connected PivotTable or a connected table, and the normal Excel refresh button will update both of them.

You pay a price for these live routes using permissions. Analyze in Excel and Export with live connection both need your tenant administrator to turn on the setting called “Users can work with Power BI semantic models in Excel using a live connection”. You also need Build permission on the semantic model or a Contributor role in the workspace that holds it, plus a Power BI licence like Fabric Free, Pro, or Premium Per User. A Fabric Free licence will only reach models in My workspace or on a Premium or F64 and above capacity.

You should know three more limits before you promise a live workbook to anyone. Microsoft says you might see an error if the data is larger than 2 GB, so you have to filter it down or use the XMLA endpoint to fix it. Mac users can only open these workbooks in Excel for the web, and people outside the tenant hosting the semantic model cannot use either route at all. This makes it hard to hand files to clients, so if your numbers need to land on a schedule without someone clicking, the answer is usually a Power Automate flow refreshing the query for you.

Why is the Power BI option missing from your Copilot pane?

People got confused by the news coverage on a second point. Microsoft’s Copilot help page says Power BI content in Add work content is available only when your admin enables Fabric data in Microsoft Copilot. The message centre write-ups from August said no admin action is required. These two true statements look like they disagree.

Both pages are right, and Microsoft’s documentation on using Power BI data in Microsoft Copilot is the page that explains why. It says the feature is enabled by default. There is a switch that controls the option, but in most tenants nobody ever had to touch it. This explains why some people saw the Power BI tab appear on its own, and others are still trying to find it.

You will find the switch inside the Microsoft 365 admin centre instead of Excel or Power BI. An administrator clicks on Copilot, goes to Settings, goes to View all, and opens Fabric data in Microsoft Copilot. Under Step 1, they choose who can reach it by selecting No users, All users, or Specific groups. If your organisation set this to No users or left you out of the specific group, that tab will never show up for you in Excel.

A second switch sits under Step 2, and it does a different job. A Fabric administrator has to enable “Share Fabric data with your Microsoft 365 services” in the Fabric admin portal tenant settings if you want Fabric data to be indexed and searchable across Microsoft 365. This setting controls whether Fabric metadata is shared with Microsoft 365. Turning it on lets Copilot go and find the right report for you, so you do not have to attach it yourself.

Microsoft adds a warning to this arrangement, and I think it is fair to repeat it. When you let Copilot users search data stored in Fabric, their query data from Copilot goes over to Fabric. Fabric operates separately from Copilot under different commitments and its own product terms. If you work with clients under a data-handling clause, their legal team will ask about this.

What does the snapshot mean for a workbook you give somebody else?

A stale number inside your own draft is just an annoyance. But a stale number inside a file that someone else uses to make a decision is a serious problem. The snapshot method hides this failure, so things break quietly instead of warning you.

Think about how a normal Excel connection tells you it is getting old. A connected workbook shows its query under Data, then Queries and Connections, and it includes a refresh button and a last-refreshed time. The workbook stays honest about its data even if nobody presses refresh. Copilot pastes your table without any of those details, because it was never a real connection.

This leaves nothing in the file to tell you when the number was true. Six weeks later, the figure in cell D12 is wrong by however much the business moved, but it still looks perfectly correct. I wrote about this exact failure in building an executive dashboard people actually trust, and the fastest way to lose a client is to give them a confident number that nobody can put a date on.

One more difference will matter to you if you answer questions about security. Microsoft states clearly that row-level security and object-level security are supported for Analyze in Excel and for Export with live connection. Row-level security is simply the rule that decides which rows each person is allowed to look at.

The Copilot help page does not say this about its snapshot, and it only says you pick from reports you have permission to access. That rule applies to the whole report, and it does not check the individual rows inside it.

I am not saying Copilot ignores those rules. I have no evidence of that, and Microsoft has not said it. But the guarantee is written down for two of the three routes, and it is missing for the third one. When a client asks you to prove a security rule, having it in writing is the only thing that matters.

So which one should you pick, and in what order?

You can make your choice by answering one question before you start. You have to ask yourself if somebody will open this sheet again after today. If the answer is no, Copilot is the fastest tool you can use. If the answer is yes, you should take the longer road now so you do not have to explain a wrong number later.

1. 01
   Ask whether the sheet has a second life

   A number for an email, a figure to settle an argument, a quick sanity check on a model. Those are all one-life sheets and Copilot is right for them.
2. 02
   If it will be reopened, start in Power BI, not in Excel

   Export, then Analyze in Excel, gives you the whole semantic model with a live connection. It is more clicks on day one and no clicks every month after that.
3. 03
   If you only need one visual, export that visual

   The visual’s More options menu, then Export data, then Summarized data, then the xlsx with live connection option. Remember the 500,000 row ceiling.
4. 04
   If the workbook already exists, use the Excel add-in

   Insert, PivotTable, From Power BI (Microsoft), or Data, Get Data, From Power Platform, From Power BI (Microsoft). Insert a connected PivotTable or connected table into the sheet you already have.
5. 05
   Write the date next to any number Copilot pasted

   One cell saying which day the snapshot was taken costs nothing and it is the only thing standing between a helpful table and a wrong decision.
6. 06
   If the Power BI tab is not there, ask your admin, not the internet

   It is a tenant setting in the Microsoft 365 admin centre under Fabric data in Microsoft Copilot. Nothing you change inside Excel will bring it back.

None of this makes Copilot a bad feature. It is genuinely quicker to ask a question in ordinary English and get a clean table back than it is to build a PivotTable. I would reach for it every time I have a one-off question.

But it is not a connection. The moment you treat it like one, you build a report that goes quietly wrong. This matches the rule I always follow in automating an Excel report properly, where you must know exactly where each number came in and exactly when it last came in.