Use Power BI data in Excel

By Office Feature PM on June 11, 2020

My name is David Monroy, and I’m a Program Manager on the Excel team. I’m excited to share that Excel data types from Power BI are now rolling out to Insiders in organizations with Office 365 E5/A5 or Microsoft 365 E5/A5. The feature was announced at the Microsoft Business Applications Summit last month (blog, conference session).

Power BI data in Excel

Data is at the core of Excel. Getting the information you need and easily refreshing it is critical to many everyday workflows. We’re giving you access to your company or organization information from Power BI as a data type in Excel. Therefore, expanding your ability to bring in linked information in your spreadsheets. This is a great addition to the PivotTable from Power BI datasets, which is already available to Insiders.

How Excel data types work

With linked data types, your cells are more than just a number or string. They can now have additional attributes from external sources. In this case, Power BI. You can now find up-to-date details from featured tables in Power BI datasets within Excel’s data types gallery and refresh on-demand. Data types offer the flexibility of the Excel grid, with the peace mind of knowing you are using up-to-date information. This allows you to use formulas, build reports, and analyze your data in a simple way.

For example, you can have a Customers table, where each cell stores all the information related to a given customer:

You can view the information in the card (shown above), or extract it to the Excel grid (shown below):

And use the Excel features you know and love, for example conditional formatting:

Scenarios to try

Make your table a featured table in Power BI

Excel uses featured tables in Power BI to add enterprise data to your worksheets. Find the details here: Access Power BI featured tables.

Convert to Excel data types

Let’s say you created an Employee table in Power BI and want to include the names of employees of your organization in a single column. Select the cells with the names, and go to the Data tab. In the Data Types group, select your Employee data type. You can also use the Organization button if you don’t know which table contains the information you need. This will search through all the featured tables in Certified datasets in Power BI.

Interact with your linked data

You can see the linked data by clicking the  button in the cell. Or by choosing a cell and inserting a field to the grid with the  button.

Refresh your data

One of the advantages of using data types is the ability to refresh your data. Right-click a cell and select Data Types > Refresh, or click Data > Refresh All.

For more information, see Use Excel data types from Power BI.

Requirements and availability

To access and use data types from Power BI, you must meet these requirements:

  • You are an Excel subscriber in organizations with Office 365 E5, Microsoft 365 E5, Office 365 A5, and Microsoft 365 A5.
  • The English editing language is added to your Office Language Preferences.
  • Power BI featured tables are enabled in Power BI, and you have access to them. See Access Power BI featured tables in Excel for considerations and limitations.

Currently, the feature is rolling out to Office Insiders in the Beta channel (formerly Insiders) in Excel for Windows. It’s coming to Excel for Mac and Excel on the web in the coming weeks!

Feedback

If you have feedback and suggestions, please let us know through Help > Feedback in Excel. Add #PowerBIDataTypes in your feedback so we get it quicker.

Learn more about what other information you should include in your feedback to ensure it is actionable and reaches the right people. We’re excited to hear from you!

With the Office Insider newsletter, you can get the latest information about Insider features in your inbox once a month. Sign up here.

Posted in Excel, Feature Deep Dives, Insider Fast, Windows Desktop

Comments are closed.