Browse 553 attributed reviews, viewable separately for our classroom and online training
The new Excel data types will make a big difference to your life
Part four of a four-part series of blogs

Soon you will be able to display information from almost any common dataset in your Excel worksheets automatically. For now your access will probably be limited to stocks and geographical data, but many more data types (including your own custom ones) should be available soon.

  1. New Excel data types will make a big difference to your life
  2. Creating and using data types
  3. The built-in data types available (and those coming soon)
  4. Power BI datasets as Excel data types (this blog)

Posted by Andy Brown on 22 December 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Power BI datasets as Excel data types

This will be a frustrating blog to write, as I just can't get the last bit of this impressive new feature to work!

Organisation data types

In addition to all of the other standard data types, you can use data types based on tables that you've published from Power BI.

Step 1 - Create a featured table in Power BI

This feature has been in preview since May 2020, but only went live in Power BI this month (December 2020).  To use a table in a Power BI dataset as an Excel data type, you must first make it a featured table.

Something went wrong

A whingey digression: when I follow the steps below on my laptop I get this message and Power BI hangs. I seem to be the only person with this problem (which I get even after uninstalling and re-installing Power BI Desktop).

 

Anyway, enough of that moaning.  To get a featured table, first load some data in Power BI Desktop:

Table of cats

Regular readers won't be surprised that I've gone for a table of cats.

 

To turn this into a featured table, do this:

Is featured table

In Model view, select the table and turn its Is featured table property on.  I had to get an old laptop out of mothballs to do this, given the problem mentioned above!

You can then give your table a description, and say which field is a label for each row, and which is the primary key (the unique row number):

Featured table set-up

When you've finished, save your changes.

Step 2 - publish your dataset

You can now publish your dataset to the Power BI Service in the usual way:

Publishing a report

Click on the Publish button to publish your report.

 

Step3 - adding your table in Excel

You should now be able to go into Excel and choose your new data type.  First type in some values:

Data table of cats

I want Excel to recognise these cats!

 

Choose to show the organisational data types for your company:

Organization data type

Choose this data type.

You should now be able to choose which Power BI dataset you want to use as a source for your data type, and then create formulae to show your cats' colour and quirks!

Except as mentioned at the start of this blog, I can't, no matter what I do.  Read on for what might be going wrong.

What can go wrong

Here are some reasons why you might not be able to see your organisation's data types:

Reason Notes
Insufficient Power BI rights One account I'm using has a paid Power BI Pro licence, and the other has a Power BI Premium user trial licence, but it's possible somehow we haven't paid the right amount of money in the right combination to Microsoft to use this feature.
Old workspace You can only use this feature if you have a new-style workspace, so you may need to ask your Power BI administrator to upgrade yours before you can share its tables.
No access to featured tables Your Power BI administrator may need to give people in your organisation access to using featured tables, as described here, although when I checked I found that this was enabled by default for our version of Power BI.
Wrong Excel version The most likely reason for things not working is that Microsoft haven't yet rolled this feature out to your version of Excel 365, in which case all you can do is wait!

I have no idea which of these is the problem on my laptop, but am frustrated I can't get this great new feature to work. 

This blog has 0 threads Add post