Many Excel users work with Power Pivot to build large data models, run DAX calculations, and create advanced pivot tables. When Microsoft added Copilot to Excel, it promised natural-language querying and automated analysis. But Power Pivot data models do not always behave like standard Excel tables. This article explains which Power Pivot features Copilot can handle and which ones cause errors or return no results. You will learn the exact compatibility boundaries and how to work around the current limitations.
Key Takeaways: Copilot and Power Pivot Compatibility
- Copilot pane > Data source awareness: Copilot reads only linked table data from the Excel Data Model, not calculated columns or measures.
- Power Pivot > Manage Data Model > Table preview: Copilot can suggest pivot tables and charts if the source data is in linked Excel tables only.
- DAX measures and calculated columns: Copilot ignores these entirely and may generate incorrect or empty results.
How Copilot Interacts with Power Pivot Data Models
Copilot in Excel works by reading the data in the current workbook. When the data comes from Power Pivot, Copilot can only see tables that were added as linked tables from Excel. Tables imported directly into Power Pivot using Power Query or external data connections are invisible to Copilot. This is because Copilot reads the Excel object model, not the VertiPaq engine behind Power Pivot.
Calculated columns and DAX measures are computed inside the Power Pivot engine. Copilot has no access to these calculated values. If you ask Copilot to sum a measure or filter by a calculated column, it will either return an error or produce a result based on the raw linked table data instead.
Power Pivot relationships are also not recognized by Copilot. When you create a pivot table from a Power Pivot data model, Copilot sees only the flattened view of the pivot table, not the underlying relational model. This means Copilot cannot suggest actions that rely on table relationships or drill-through paths.
What Works: Supported Power Pivot Scenarios
Copilot can handle a few specific scenarios involving Power Pivot data, provided the data is in linked tables. The following list explains what you can expect to work.
- Linked table analysis
If you add a table to the Excel Data Model using Power Pivot > Add to Data Model while the table is still in an Excel sheet, Copilot can read that table. You can ask Copilot to summarize, filter, or sort the data in that linked table. - Pivot table suggestions from linked tables
When you select a linked table and open the Copilot pane, Copilot may suggest creating a pivot table or chart. These suggestions use the raw linked table data, not the Power Pivot model. - Chart creation from linked table data
Copilot can generate a chart from a linked table. The chart uses the data as it appears in the sheet, not from the Power Pivot model. This works for simple bar, column, and line charts. - Natural language queries on linked table columns
You can type questions like “show total sales by region” if the columns exist in the linked table. Copilot will filter or aggregate the data in the sheet.
What Fails: Unsupported Power Pivot Scenarios
The following Power Pivot features are not compatible with Copilot. Attempting to use them will produce errors, empty results, or incorrect data.
- DAX measures
If your Power Pivot model contains measures like TotalSales:=SUM(Sales[Amount]), Copilot cannot read or evaluate them. Asking “what is total sales” will return either an error or a result based on a simple column sum in the linked table. - Calculated columns
Columns created with DAX formulas in Power Pivot are invisible to Copilot. Any request that references a calculated column will fail. - Imported tables from Power Query or external sources
Tables brought into Power Pivot through Power Query or direct database connections are not visible to Copilot. Only linked Excel tables are accessible. - Table relationships
Copilot does not understand relationships defined in Power Pivot. Asking “show customers who bought product X” will not work if the data spans multiple related tables. - KPIs and hierarchies
Power Pivot KPIs and date hierarchies are not recognized. Copilot will ignore them and may return flat data instead.
Common Issues When Using Copilot with Power Pivot
Copilot returns “I can’t find that data” when you reference a Power Pivot table
This occurs when the table was imported into Power Pivot using Power Query or an external connection. The table is stored in the VertiPaq engine and is not part of the Excel object model. To fix this, recreate the table as a linked table by selecting the data in Excel and choosing Power Pivot > Add to Data Model. The original sheet must remain in the workbook.
Copilot generates a pivot table that shows incorrect totals
When Copilot creates a pivot table from a linked table, it uses the raw data in the sheet. If the Power Pivot model contains measures that aggregate data differently, the Copilot-generated pivot table will not match the Power Pivot version. To avoid confusion, use Copilot only on linked tables that do not have measures or calculated columns.
Copilot suggests a chart but the chart is empty
An empty chart usually means Copilot tried to read a Power Pivot table that is not linked. Copilot sees no data rows because the table exists only in the Power Pivot engine. Move the data to a regular Excel table or use a linked table approach.
Copilot cannot apply filters to a Power Pivot pivot table
Copilot can only filter data that exists in the sheet. A Power Pivot pivot table displays aggregated results from the model, not the raw rows. Copilot cannot modify the underlying Power Pivot query. If you need to filter the pivot table, use the standard pivot table filter fields manually.
| Item | Copilot and Linked Table | Copilot and Imported Power Pivot Table |
|---|---|---|
| Data visibility | Full read access | No access |
| DAX measures | Not supported | Not supported |
| Calculated columns | Not supported | Not supported |
| Table relationships | Not recognized | Not recognized |
| Pivot table suggestions | Works | Fails |
| Chart creation | Works | Fails |
| Natural language queries | Works on raw columns | Fails |
You can now determine which Power Pivot scenarios are safe to use with Copilot and which require manual work. For any Power Pivot model that relies on DAX measures or imported tables, continue using the Power Pivot interface directly. If you need Copilot assistance, consider creating a separate linked table that mirrors the model data. This gives you Copilot features without disrupting your existing Power Pivot setup. As a next step, test a small linked table with Copilot to confirm the behavior matches your expectations before moving larger models.