You want to analyze data stored in Microsoft OneLake directly inside Excel using Copilot. OneLake acts as a single data lake for your entire organization, but connecting it to Excel requires specific steps. This article explains how to connect Excel to OneLake data and enable Copilot to generate insights, formulas, and visualizations from that data.
OneLake is part of the Microsoft Fabric platform and stores data in Delta Parquet format. Excel can read this data through Power Query, but Copilot needs the data to be in an Excel table or a Power BI dataset to work. This guide covers both methods: importing OneLake data into an Excel table and connecting to a Power BI dataset that sources from OneLake.
By the end, you will be able to use Copilot in Excel to ask questions about your OneLake data without leaving the spreadsheet. You will also learn the limitations of each approach.
Key Takeaways: Excel Copilot with OneLake Data
- Excel > Data tab > Get Data > From Other Sources > From Microsoft Fabric: Imports OneLake data into an Excel table so Copilot can analyze it.
- Power BI dataset connection: Enables Copilot to work with live OneLake data without importing into Excel tables.
- Delta Parquet format requirement: OneLake stores data as Parquet files; Excel must convert them to tables for Copilot to function.
How OneLake Data Connects to Excel Copilot
OneLake is the unified data lake in Microsoft Fabric. It stores data in Delta Parquet format, which is optimized for analytics but not directly editable in Excel. Copilot in Excel can analyze data only when that data is in an Excel table or when it is connected via a Power BI dataset that is certified or promoted.
There are two main methods to bring OneLake data into Excel for Copilot:
Method 1: Import OneLake Data into an Excel Table
You use Power Query to pull data from OneLake into a standard Excel table. Once the data is in a table, Copilot can generate formulas, charts, and insights. This method creates a static snapshot unless you refresh the query.
Method 2: Connect Excel to a Power BI Dataset Based on OneLake
If your organization has a Power BI dataset that sources data from OneLake, you can connect Excel directly to that dataset. Copilot can then analyze the live data without importing it into Excel tables. This method requires the dataset to be in a Premium workspace and to be certified or promoted.
Prerequisites for both methods:
- A Microsoft Fabric capacity with OneLake enabled
- Access to the OneLake data or the Power BI dataset
- Excel for Microsoft 365 with Copilot enabled (Microsoft 365 Copilot license)
- Power Query in Excel (built-in for Microsoft 365)
Steps to Import OneLake Data Into an Excel Table for Copilot
Follow these steps to pull data from OneLake into Excel and then use Copilot.
- Open Excel and go to the Data tab
Click the Data tab on the ribbon. Look for the Get Data group in the top-left corner. - Select Get Data > From Other Sources > From Microsoft Fabric
In the dropdown menu, choose From Other Sources, then select From Microsoft Fabric. This option connects to OneLake and other Fabric artifacts. - Sign in to Microsoft Fabric
If prompted, sign in with your work or school account that has access to the OneLake data. Excel will display a list of available Fabric items. - Choose the OneLake data source
Select the Lakehouse or Warehouse that contains your data. Then choose the specific table or file you want to import. Click Load to import the data into a new Excel worksheet. - Convert the imported data to an Excel table
After loading, the data appears as a query table. To use Copilot, convert it to an Excel table: click anywhere in the data, press Ctrl+T, and confirm the table range. Name the table meaningfully, for example SalesData. - Open the Copilot pane
Click the Copilot icon on the Home tab or press Alt+Windows logo key+I. The Copilot pane opens on the right side of the Excel window. - Ask a question about your OneLake data
Type a natural language question such as Show total sales by region for the last quarter. Copilot analyzes the table and generates a formula, chart, or summary.
Steps to Connect Excel to a Power BI Dataset Based on OneLake
If you prefer live data without importing, use a Power BI dataset that sources from OneLake.
- Confirm the Power BI dataset is certified or promoted
Only datasets marked as certified or promoted in the Power BI service can be used by Copilot in Excel. Ask your Power BI admin to certify the dataset if needed. - Open Excel and go to the Insert tab
Click the Insert tab on the ribbon. Look for the Power BI group on the right side. - Click Power BI > From Power BI
Select From Power BI from the dropdown. Excel opens a pane showing available datasets. - Select the OneLake-based dataset
Choose the dataset that sources data from OneLake. Click Connect. Excel adds a PivotTable or a new sheet linked to the dataset. - Open the Copilot pane
Click the Copilot icon on the Home tab. Copilot recognizes the connected dataset and allows you to ask questions about the live data. - Ask a question
Type a question like What is the average order value by product category? Copilot returns results based on the live OneLake data.
Common Mistakes and Limitations When Using Copilot With OneLake
Copilot Does Not Appear in the Home Tab
If the Copilot icon is missing, your Excel version may not support it. Copilot requires Excel for Microsoft 365 version 2402 or later. Check your version under File > Account > About Excel. Also verify that your Microsoft 365 Copilot license is active.
Copilot Says It Cannot Analyze the Data
Copilot works only with Excel tables, not with raw query tables from Power Query. After importing OneLake data, always convert the query output to an Excel table using Ctrl+T. If the data is connected via Power BI, ensure the dataset is certified or promoted.
Data Refresh Fails After Import
Imported OneLake data is static unless you refresh the Power Query connection. To refresh, go to Data > Queries & Connections, right-click the query, and select Refresh. You can also set automatic refresh in the query properties.
Copilot Returns Incorrect Results With Live Data
When using a Power BI dataset, Copilot relies on the dataset’s measures and columns. If the dataset has incorrect relationships or calculated columns, Copilot may generate wrong answers. Verify the dataset in Power BI Desktop before using it in Excel.
Copilot in Excel With OneLake Table vs Live Dataset: Key Differences
| Item | Imported Excel Table | Live Power BI Dataset |
|---|---|---|
| Data freshness | Static until manually refreshed | Always live |
| Copilot features available | Full: formulas, charts, insights, conditional formatting | Limited: insights and summaries only, no formula generation |
| Setup complexity | Low – import and convert to table | Medium – requires certified dataset in Premium workspace |
| Performance | Fast after import | Depends on dataset size and query speed |
Choose the imported table method when you need full Copilot features and can accept a static snapshot. Choose the live dataset method when you need up-to-date data and your organization has certified Power BI datasets.
Now you can connect Excel Copilot to OneLake data using either the import method or the live dataset method. Start by identifying which approach matches your data freshness needs. For advanced analysis, consider creating a Power BI dataset with custom measures that Copilot can query directly. Remember to always convert imported data to an Excel table before using Copilot.