Power BI Organizational Data Types in Excel

Power BI Organizational Data Types in Excel allow you to store attributes about your data in a single cell. This rich data can then be referenced with the formulas we know and love.

Imagine having a single cell that contains everything you need to know about a product or employee or a company, to name a few. This rich data might include images, pricing, stock on hand and much more.

In the example below I’ve created a list of our courses as an organizational data type. You can see when I click on the data type icon to the left of the course name the card opens showing the data available in the cell:

You can also use formulas to reference the data and or bring it into other cells and perform calculations on it:

You can even display resizable images from the data type in a cell:

We store the organizational data in Power BI and users who have permission can reference it from any Excel file. This vastly reduces the size of our Excel files with Power BI acting as a storage repository that can be managed and controlled in a single central location.

Updating the data is easy and with a click of the Refresh All button users can get the latest information.

Examples of uses for Power BI Organizational Data Types in Excel include invoicing, quoting, and planning to name a few.

Licensing Requirements

Note: Currently requires English added as an editing language (more languages coming soon).

Watch the Video

Try Power BI Organizational Data Types in Excel - Download Workbook

Setting Up Power BI Organizational Data Types

1. Create data either in Excel or use data from another database. My example data is in an Excel workbook saved on SharePoint. I then imported it into Power BI Desktop using Power Query. See this tutorial for getting files from SharePoint with Power query.

Power BI Desktop

Once you've imported your data to Power BI Desktop there's a little set up to do:

2. Go to the Model View > Table Properties – turn on ‘Is featured table’:



At the dialog box (shown below) give your featured table a description. If you start the description with "Featured table" it will help Power BI report creators identify it.



The Row label field is used in Excel so users can easily identify the row. It appears as the cell value for a linked cell in the Data Selector pane, and in the Information card.

The Key column field value provides the unique ID for the row. This value enables Excel to link a cell to a specific row in the table.

3. In the Data view > Column tools tab set the data category for any URL fields you have. I’ve got a Web URL for the syllabuses and an Image URL for the course logos:

4. Save the Power BI Desktop file
5. Publish to Power BI Service:

Note: make sure you choose a Modern Workspace when publishing.

Power BI Service

In the Power BI service, we set permissions and refresh capabilities.

6. Give users access: Open the Dataset Settings: Add users or groups to the dataset:



7. Set up the Data Source Credentials so your data can be refreshed:

Note: if your data isn’t stored in the cloud you’ll need to set up the Gateway connection (see option above ‘Data source credentials’ in the image above).

Excel

8. Restart Excel: before you can access the organizational data types you must restart Excel as it caches data types.
9. Open Excel again and you should see your organizational data types in the Data Type gallery:



10. Select the cells containing the values for the data types (in my example it’s the course names), then click the relevant organizational data type from the data type gallery:

If there are any ambiguities the Data Selector pane will open and you can choose the correct match from the list:

More on Data Types

Power Query Custom Data Types – Includes tips for referencing data types in formulas and more.

Built in Data Types - Stocks, Currency and Geography.