Organizational Excel Data Types

Microsoft introduced stocks and geography data types into Excel last year, but they never really caught my attention as I couldn't see their relevance to my own data. Just a few days ago they have announced the release of Organizational Data Types in Excel, giving us the ability to bring the awesome functionality to our own data. Now you've got my attention Microsoft!

How to:

Here's a demo on how to setup Organizational data types for your company in Excel.

Step 1 - Turn on Featured Tables

Featured Tables is a Power BI desktop preview feature. You need to turn this on first in Power BI desktop, and then restart. See the Microsoft documentation for this here: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-create-excel-featured-tables

Step 2 - Build Power BI Dataset with Dimension Tables

Create a dataset in Power BI. This can be specific to one team, or data that is shared across the entire company. One table that pretty much everyone will want access to is Dates. In my example, I am going to use a Course List to make it easy for the Sales team to see key information about our course offerings.

Connect to your data from within Power BI desktop. In the Model view, set each dimension table as a Featured Table. If you aren't familiar with Dimension tables, check out my posts on relationships in Power BI. If you are familiar with Dimension tables, you'll note that setting up a featured table requires you to select the unique identifying information for that dimension. In this example, we will use Course Title: 

Step 3 - Publish Power BI Dataset

Publish your Power BI dataset, with featured tables enabled, to Power BI web service. Choose a workspace based on the team that needs access to the featured table. If it's specific to the Sales team, publish to a Sales team workspace. If it's company wide, publish to a company wide workspace. 

Note: You must publish to a NEW workspace. Classic Power BI workspaces do not support Excel Data Types. See this Microsoft documentation for details on how to upgrade to a new workspace: https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-upgrade-workspaces

Step 4 - Open Excel and use the Data Type

If you had Excel open during the previous steps, restart Excel. Newly created Data Types won't show up until you restart. 

In the Data tab in Excel, you will now see your new Organizational data types. Note I have Calendar and Courses available to use. 

To use the Data Type: 

1. Type a list of values that match the values from the 'Row label' column in the featured table. In this example, I will type a few course titles. 
   
2. Select the data, and click the Data Type from the Data tab in the ribbon.
3. If Excel finds an exact match to your value in the featured table, it will display the icon for that Data Type. 
   
   
   
4. If Excel can't find an exact match to your value, it will display a question mark icon

Resolving unmatched values

To resolve the unmatched values, simply click on the Question Mark icon. This will open a pane on the right hand side of Excel. Choose the matching value, click Select, and Excel will store this match for you, and remember it for this data range or table.

Step 5 - Use Data Types in Excel Tables

Turn your data into an Excel table (Ctrl T shortcut works a treat for this one). Now you can easily add columns to your table by pulling information from the featured table. No VLOOKUP required! 

And yes, it continues to pull that information through for future rows of the table. Have a look at the formula bar - it will look something like this: 

=[@[Course Title]].Level

If you've used Table formulas before, this will look vaguely familiar - it's referring to the column name from our featured table in the Power BI dataset.