New ways of connecting Excel Workbooks to Power BI

Microsoft announced new capabilities to the already introduced Connected Tables in Excel sheets and future changes to how Excel files are supported in Power BI workspaces.

There are several new ways to connect Excel to Power BI Tables but also from Fall 2023 some features for using Excel Workbooks in Power BI Workspaces will no longer be available.

Let’s see what possibilities will be available to connect Excel to the Power BI data model.

Insert a Connected Table to Excel

A new option of Insert Table is rolling out for the Power BI Datasets pane in Excel. It enables users to create a connected table directly in Excel. This streamlines the workflow of adding data and is friendlier to more Excel users.

This option is going to be available in the Power BI Datasets side pane in Excel:

As a result, a Connect Table is inserted into the worksheet. You can refresh the data to get the latest data. Any data security like Row Level Security, Permissions, and Sensitivity Labels are enforced as well.

Just like with other Connected Table experiences, the workbook contains a Connection that you can review and modify through the Queries and Connections pane. You can even change the query if you’d like using the Command Text on the Definition tab.

Create a connect workbook from the OneLake Data Hub

Microsoft is also starting to roll out a new Export with live connection (.xlsx) option for the OneLake Data Hub experiences.

When you navigate to a dataset in Power BI, you can use the Tables side pane to pick a table.

Within the Export menu a new Export with Live connection (.xlsx) option is available. This creates a connected Excel workbook that shows a Connected Table just like we showed above, or with the Export to Excel capability when viewing an interactive report.

This experience is great for creating new queries, especially for those who don’t know how to write DAX. There are several limitations to be aware of:

• The Insert Table option is rolling out to Excel Desktop users incrementally. It won’t be available immediately in Excel for the Web.
• The Export with Live connection (.xlsx) is rolling out for Power BI users in the Data Hub.
• The Column names in Excel can’t be changed. If you refresh, the query will reset the column names to the original ones.
• You can’t edit the query in the UI. If you’d like to make changes, it’s best to just create a new query with the settings you want.

Changes from Fall 2023

As for using Excel files in the Power BI Service, Microsoft is announcing the deprecation of some features for Excel workbooks in Power BI workspaces.

The following features of Excel workbooks in Power BI workspaces will be changing:

Beginning September 29, 2023:

• Uploading local workbooks to Power BI workspaces Excel files that don’t already have scheduled refresh configured will no longer be allowed will no longer be allowed 
• Configuring scheduling of refresh and refresh now for all Excel files that don’t already have scheduled refresh configured will no longer be allowed

Beginning October 31, 2023:

• Scheduled refresh and refresh now for existing Excel files that were previously configured for scheduled refresh will no longer be allowed
• Local workbooks uploaded to Power BI workspaces will no longer open in Power BI

After October 31, 2023:

• You can download existing local workbooks from your Power BI workspace
• You can publish your Excel data model as a Power BI dataset and schedule refresh
• You can import Excel workbooks from OneDrive and SharePoint Document libraries to view them in Power BI

What should you do if you’re affected by these changes?

There are several options, such as:

• For local workbooks, download them and republish them using SharePoint or OneDrive for Business, then import them as a reference to the Power BI workspace.
• For scheduled refresh and refresh now, publish the data model from the Excel workbook as a Power BI dataset. Then configure refresh for the Power BI dataset.

The following capabilities are continuing to be supported:

• Your workbook from Power BI will not be removed. You can use the new download option to download any local workbooks you have stored in Power BI workspaces.
• You can continue to upload Excel workbooks from OneDrive or SharePoint Document Libraries into Power BI workspaces.
• You can continue to publish Excel models as Power BI datasets and schedule refresh for those datasets.

We recommend that you use Power BI Desktop to import your Excel models because it upgrades the data model to the latest version which gives you the best experiences.

What should you do if your Excel workbook is affected by these changes?

Use the Download Excel file option to download any local workbooks from your Power BI workspaces. You can add them to Power BI again by first saving them to OneDrive for Business or a SharePoint Document library (ODSP), and then importing the workbook from ODSP to the workspace.

You can publish your Excel data models to Power BI as a dataset to configure refresh for these data models in Power BI service, in Excel, or in Power BI Desktop. It is recommended to import the data model from Power BI Service to the Power BI Desktop and then publish it as a dataset. This is preferred since it upgrades your data model to the latest version which gives you the best experiences.

To build new workbooks connected to the data model in your Excel workbook, you’ll first publish the data model as a Power BI dataset as described above. Then in Excel you canconnect your workbook to the dataset.