Power Query Nested Data Types In Excel

A year ago support for nested data types in Excel was announced on the Excel blog, but the announcement didn’t have much detail about what nested data types are and the docs are quite vague too. I was recently asked how to create a nested data type and while it turns out to be quite easy, I thought it would be good to write a short post showing how to do it.

Let’s say you have a Power Query query that returns details of the different types of fruit that you sell in your shop:

Let’s also say that the last three columns in this table (Grower, Address and Specialty) all relate to the farmer that supplies you with this fruit. Now you could create one Excel data type with all these columns in, but nested data types allow you to create data types inside data types, so in this case you can create a data type specifically for these three columns relating to the farmer and then nest it inside the main data type.

To do this, select these three columns in the Power Query Editor and click the Create Data Type button on the Transform tab in the ribbon:

Give the data type a name, in this case Farmer, in the Create Data Type dialog:

At this point you’ll have a query that returns a table where one of the columns, Farmer, contains a data type:

Finally, you then select all the columns in this table, including the Farmer column, and click the Create Data Type button again to create another new data type, this time called Product:

Here’s what you’ll see in the Power Query Editor once you’ve done this:

And here’s the result in the Power Query Editor:

Once you’ve loaded this query to the worksheet you can explore the nested data type via the card popup:

Or you can access the data in the nested data type using a formula. For example, in the animated gif above the cell A2 contains the data type for Apples, so the formula

=A2.Farmer.Address

…returns the address of the farmer who grows apples.

Alternatively, you can use the Excel FieldValue function to get the same result:

=FIELDVALUE(FIELDVALUE(A2, "Farmer"), "Address")