Exciting news! New capabilities have been introduced to simplify the process of adding Power BI data to Excel workbooks. Earlier this year, the “Export with Live Connection” feature was unveiled, allowing users to create Connected Tables within Excel sheets from interactive reports. Now, these user-friendly enhancements are being expanded to more areas to benefit a wider range of users. Let’s explore the latest updates.
These capabilities are currently being rolled out and are expected to become available over the next few weeks.
Insert a Connected Table without leaving Excel
Excel users appreciate the familiarity of the Excel grid. Now, a new “Insert Table” option is being introduced for the Power BI Datasets pane in Excel, allowing users to create a connected table directly in Excel. This streamlines the process of adding data and is designed to be more user-friendly for a broader audience of Excel users. Here’s 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
Additionally, a new “Export with live connection (.xlsx)” option is being introduced 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.
A new addition to the Export menu is the Export with Live connection (.xlsx) option. This feature enables the creation of a connected Excel workbook that includes a Connected Table, similar to the method described earlier. Additionally, it offers the option for export to Excel when viewing 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.
If you don’t see these capabilities immediately, just give it some time since the roll-out can take several weeks to fully complete.
