You want Copilot in Excel to analyze data that was transformed or loaded using Power Query, but Copilot may not respond to queries after a refresh. This happens because Copilot reads the current state of the worksheet, not the Power Query engine directly. This article explains how to structure your Excel workbook so Copilot can work with Power Query output, and it provides the exact steps to refresh data and run Copilot queries successfully.
Key Takeaways: Using Copilot with Power Query Refreshed Data
- Data > Queries & Connections > Refresh All: Refreshes the Power Query output so Copilot sees the latest data.
- Copilot pane > Ask a question: Copilot answers based on the visible Excel table, not the Power Query transformation steps.
- Format as Table (Ctrl+T): Converts Power Query output into a structured table that Copilot can reference reliably.
How Copilot Interacts With Power Query in Excel
Copilot in Excel works on the data that is currently displayed in the worksheet. It does not read Power Query transformation steps or the query editor directly. When you refresh a Power Query, the output table updates in the worksheet, and Copilot then reads that updated table. If the output is not formatted as an Excel table, Copilot may not recognize the data range correctly, which leads to errors or empty responses.
Power Query is a data connection and transformation tool. You use it to import, clean, and shape data from sources like CSV files, databases, or web pages. After you apply the transformations, you load the result into a worksheet. Copilot sees this result as a static or dynamic range. For Copilot to work reliably, the output must be an Excel table. This gives Copilot a defined structure with column headers and row boundaries.
Prerequisites for Using Copilot With Power Query
Before you start, confirm these items are in place:
- You have a Microsoft 365 subscription that includes Copilot for Microsoft 365.
- Your Excel version is current with the latest updates from the Monthly Enterprise Channel or Current Channel.
- Your Power Query output is loaded to a worksheet, not only to the Data Model.
- The output range is formatted as an Excel table. Use Ctrl+T to convert a range to a table.
- Your workbook is saved to OneDrive or SharePoint so Copilot can access the data source.
Steps to Use Copilot After a Power Query Refresh
- Open the workbook and go to the worksheet with Power Query output
Make sure the worksheet contains the table that Power Query loads data into. If you have multiple queries, open the sheet that has the main output table. - Refresh the Power Query data
Go to the Data tab on the ribbon. In the Queries & Connections group, click Refresh All. This runs all Power Query transformations and updates the output table in the worksheet. Wait for the refresh to complete. You can see the status in the lower-left corner of the Excel window. - Verify the output is an Excel table
Click anywhere inside the output range. If you see the Table Design tab on the ribbon, the range is already a table. If not, press Ctrl+T, confirm the range, and click OK. Give the table a meaningful name in the Table Design tab, for example SalesData. - Open the Copilot pane
Click the Copilot button on the Home tab of the ribbon. The Copilot pane opens on the right side of the Excel window. - Ask a question about the refreshed data
Type a question in the Copilot text box. For example, type Show total sales by region or What is the average order value for the last 30 days? Press Enter. Copilot analyzes the table and returns a summary, a chart suggestion, or a formula. - Insert the result or apply a formula
If Copilot suggests a formula, click Insert to add it to the worksheet. If it suggests a chart, click Add to sheet. The result is based on the refreshed data. - Repeat after each future refresh
Any time you refresh the Power Query, repeat steps 2 through 6. Copilot always reads the current state of the table, so a fresh refresh ensures accurate answers.
Common Mistakes When Using Copilot With Power Query
Copilot Returns No Results or Says Data Not Found
This usually happens when the Power Query output is not formatted as an Excel table. Copilot needs a structured table to identify columns and rows. Convert the output range to a table using Ctrl+T. Also confirm that the table has no merged cells or blank rows at the top. Copilot reads the first row as headers, so the first row must contain column names.
Copilot Shows Data From Before the Refresh
If Copilot returns stale data, the Power Query output may not have been refreshed before you opened the Copilot pane. Click Data > Refresh All and wait for the status to show Refresh complete. Then ask your question again. If the workbook has manual refresh settings, change the Power Query property to refresh automatically when the file opens: right-click the query in the Queries & Connections pane, select Properties, and check Refresh data when opening the file.
Copilot Cannot Reference Data From the Data Model
Power Query can load data only to the Data Model without creating a worksheet table. Copilot cannot read the Data Model directly. To fix this, edit the Power Query query and change the load destination: right-click the query, select Load To, choose Table, and select an existing worksheet or a new worksheet. After you reload, Copilot can see the data.
Copilot Features vs Power Query Capabilities: Key Differences
| Item | Copilot in Excel | Power Query |
|---|---|---|
| Data source | Reads the visible worksheet table | Connects to external sources like databases, web, and files |
| Transformation | Can suggest formulas and charts but does not transform raw data | Performs filtering, merging, pivoting, and cleaning |
| Refresh behavior | Requires manual refresh of Power Query before answering | Refreshes data on demand or on file open |
| Output format | Requires an Excel table for analysis | Outputs to a table, a PivotTable, or the Data Model |
| Best use case | Asking questions about transformed data | Preparing and shaping data before analysis |
After completing the steps in this article, you can reliably use Copilot to analyze data that was transformed by Power Query. Next time you refresh a query, open the Copilot pane and ask a question about the updated data. For advanced scenarios, consider naming your Excel tables with descriptive names like Orders or Inventory so Copilot can reference them more accurately in formula suggestions.