About one year ago, Microsoft has announced the preview of the so called “Organization data types” for Excel and Power BI that add “connected data types” on top of the standard strings and numbers options.
A connected data types (based on a shared Power BI dataset) gives you access to a lot of related information of your data. Once cells have been converted into linked data types, you can then extract additional information to work within your Excel spreadsheets or your Power BI report.
As an example, if you have a data inside cells into your Excel spreadsheet, with Organization data types defined in Power BI you can:
- Select the Excel content you want to convert.
- In the Data Types group of the Data tab, select the button for the associated Power BI data and then convert using your organization’s product data type:
Cool, isn’t it? But why I’m talking about this feature here?
Because Organization data types for Excel and Power BI supports Dynamics 365 Business Central entities too!
You can define data types specific for your organization (that comes from your Dynamics 365 Business Central data):
and then you can convert your cells values to a Dynamics 365 Business Central data type. Here for example I’m converting the selected columns (where I have some customer’s names) to a Customer data type:
When converted to a data type from your organization, you can expland the details of the data type you need on your workbook (this works on cells, tables and more):
and new columns with the expanded data will be added:
How is this working?
Excel Organization Data Types from Business Central rely on the standard Power BI Connector (so, OData). You define the data type in Power BI and then you can share it to your organization’s users.
From Power BI Desktop, click on Get data and then connect to your Dynamics 365 Business Central environment by using the standard Business Central connector. Here, select the entity that you want to use (Items for example) and then on the Properties window set the Is featured table to Yes:
Then click on the Edit link in order to edit the properties of the featured table and then save:
To share your newly created data type, on the Power BI Desktop ribbon click on Publish in order to publish it on the right workspace:
That’s all.
I think that this feature is quite interesting if your customer is using Power BI and/or Excel a lot and having a set of data types shared across the organization can help on reporting and analysis.
Some notes about this feature:
- It works for both the online and the on-premise environment.
- It supports standard and custom entities (APIs).
- It supports numerical, text and blob values (like images).
- You can combine data that comes from Business Central and also from other Power BI data.
- The data types are refreshed automatically.
- Power BI users with a free license can consume the data types.
Stefano Demiliani 