Organizational Data Types in Excel using Power BI datasets

[AllisonKennedy]

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. 

[Aielar]

Hi @AllisonKennedy amazing article, thanks! What are exactly the prerrequisites to use this incredible feature? Both from MS Excel and PowerBI licensing side. Thanks!

[AllisonKennedy]

@Aielar it's a preview feature, so you need to turn it on as I mentioned in the blog article. Use with the latest version of Excel 365 (desktop at the moment) and you must have access to the Power BI dataset that has been marked as featured table, so same licensing applies as for Power BI sharing. Hopefully that helps clarify? If not, let me know and I can get into more specifics or link you to some other fantastic blogs on this community about licensing (@Greg_Deckler just updated one a few weeks back from memory).

[Anonymous]

Hello @AllisonKennedy 

I did implement it with success.

However most of Microsoft articles suggest a Power Bi Pro licence is required to consume tha data in Excel.

Do we know if a customer with no pro licence but a Premium Workspace could consume the data?

Best regards

Romain

[AllisonKennedy]

@Anonymous That is a good question. Premium workspace is greater than Power BI pro, so as long as the user has access to the Power BI dataset they should also have access to consume the Excel Organizational Data Types within that workspace. I have not tested this yet though.

[kathleeng23]

@AllisonKennedy I love these organizational data types.  I have setup several and I can see them in Excel but no one else can.  They have access to the data model used and the data types are published in the same workspace as apps they can access without issue.  Is there some other access they need to be able to use these?

[AllisonKennedy]

What access do they have to the workspace @kathleeng23 , I'm not quite sure what the issue may be in your case, but they need to have access to the workspace, not just the app...