How can we create a custom data type?

The inbuilt data types in Excel come from Microsoft (and a lot of them through various third-party partnerships that Microsoft has built, mostly with Wolfram Alpha). These let you draw down information about a lot of common categories of thing, like currencies, cities, and so on. But in newer Excel versions, you also have the ability to create your own data type. This is essentially a different way of storing and accessing tabular data. To create one, you need to use Power Query.

The data that will make up your data type could be stored in a pre-existing Excel Table, or like with any query, it could be retrieved and edited from an external source. We’ll use that as an example for our case, by creating a connection to this table in the Wikipedia article “List of highest mountains on Earth”:

There are a fair few steps required to transform this table into the format we want, but that’s outside the scope of this blog. If you want to see more about how this data is brought into Power Query and transformed, you can check out the query steps in the accompanying file. However for our purposes we will take the query when it looks like this:

To create our custom data type, we just select the column we want to use as the main index (in our case, the names of the mountains as shown above), and then use Transform => Create Data Type. The Advanced menu lets you decide which columns will be included in that type:

This collapses all the other data behind our chosen display column:

We can now load this into Excel – note that we have to load it into an Excel Table:

As with other data types, we can now use the Add Column button, at the top right, to extract data from the query and dump it in the sheet:

Why would you make a custom data type?

Custom data types let you export a Power Query result into a table on a partial basis – only outputting the columns you want, as you want them. However, unlike Excel’s in-built data types, with custom data types you cannot apply the type to a partial list of data somewhere else. If you have a list of a few mountains somewhere else in Excel, you have to output the entire data type somewhere, and then lookup the relevant data using standard Excel formulas instead:

Furthermore, you can’t use a lookup formula from a cell value like “Mount Everest” to the ‘Data type’ column in the exported table – the cell with the data type won’t match just the name field. Instead, you have to add columns for both the output data you want, as well as the name field you are matching to.

What this means is that there are very few advantages to creating a custom data type over just exporting the entire query into a table. For now, this functionality is still limited – but hopefully with time it will grow to be able to do more.

Don’t forget to check out the accompanying file if you want to see what this looks like in practice.

Previous post - PivotTable layouts
TOTW index 
Next time – How to review a spreadsheet!