We’re excited to announce new capabilities that make it easier for users to add Power BI data to their Excel workbooks. Earlier this year, we introduced Export with Live Connection for interactive reports. It made it easy for users to create Connected Tables in Excel sheets. Now we’re excited to bring the same simplicity of experience to more places so that more users can easily find it and benefit from it. Let’s take a look at the new updates.
These capabilities have started to roll out and we expect them to be available for the next few weeks.
Insert a Connected Table without leaving Excel
Excel users love the Excel grid. We’re excited that a new Insert Table option 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. Let’s take a look at how it works.
To start, use the Data Ribbon 🡪 From Power Platform 🡪 From Power BI (Microsoft) option to launch the Power BI Datasets side pane in Excel.
For each dataset, you’ll see the new Insert Table option. This option is initially available in Excel Desktop.
The new Create Table dialog opens. You can use the Data pane to select the fields you want to add to your Excel workbook.
You can use the Build pane to reorder, remove fields, and change the aggregation for fields.
Use the Filters pane to do basic filtering for rows in your table.
When you’re ready, press Insert Table to add a Connected Table to your Excel sheet.
Then a Connect Table is inserted into your 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 Data Ribbon 🡪 Queries and Connections 🡪 Queries and Connections pane 🡪 Connections Tab 🡪 Right Click 🡪 Properties. 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
We’re 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.
We’re adding to the Export menu a new Export with Live connection (.xlsx) option. 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 reports.
Things to know about
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.
Give it a try
We’re excited by these changes because they make it easier for more users to connect to up to date and authoritative Power BI data in Excel. We’d love to hear from you. Leave a comment below or add an idea to https://ideas.fabric.microsoft.com/.