Using Data Types in Excel for easy-to-create management accounts

When I first read about the ability to create Excel Data Types using Power Query, I probably over-estimated its potential. Having thought about it a bit more and experimented with creating Data Types, I have downgraded my initial assessment from earth-shattering to quite interesting. However, it's certainly still worth investigating. Note that this feature is currently available to Office Insiders as a Preview so, depending on the upgrade channel you have chosen, it might not yet be available in your version of Excel.

For those who have not yet come across Data Types in Excel, these are a relatively recent addition to the Data Ribbon tab. The new Data Type group includes two built-in Data Types: Stocks and Geography. The basic idea is that you can designate the contents of a cell, or perhaps more usefully an entire Excel Table column, as being one of the available Data Types. Then, for the built-in Data Types, Excel will check the cell entry or entries and, if it can identify them as being a valid item within that Data Type, will allow for further values or columns of values to be filled in by looking the values up online:

Several Excel Community Archive articles cover Data Types in more detail but, at the time of writing, it is not possible to search for any of them.

The new Data Type preview feature allows a Power Query query to be used as the source of Data Type values. The creation of such a Data Type is straightforward. Within the Power Query editor, you select multiple columns and use the Transform Ribbon tab, Structured Column group, Create Data Type option. This collapses the selected columns into a single column. Once the query with the collapsed column is loaded to a worksheet table, the column behaves in a similar way to one of the built-in Data Types allowing the user to choose which of the collapsed columns to display:

The above example is based on extracting columns from an Excel worksheet and then reorganising them so that they can be used as part of the Data Type. Here is the original worksheet:

Given that the process of creating our Data Type is so straightforward, we'll also cover a couple of other useful Power Query techniques. First of all, we need to click on any cell in our data and choose the Data Ribbon tab, Get & Transform Data group, From Table/Range option. This will convert our data into an Excel Table and ask whether our Table has headers. We will leave this option set to no.

Here is our data in the query editor. Our first task is to sort out the headers which are not currently in an ideal format. We want to do this in a way that makes the process as automatic as possible, rather than just treating the first row as a header row and changing the column names manually. Accordingly, we will use the Transform Ribbon tab, Table group, Transpose option:

This is our query with the rows and columns transposed:

With our query transposed, we can right-click in our first column heading and use Fill, Down to copy the Actual and Budget headings to the rows beneath them that contain null. We can then use the Home Ribbon tab, Transform group, Use First Row as Headers option to promote our first row to become the table headers:

We want to combine Column1 and Account to form the column headings for our Excel Data Type so we select the two columns, right-click in either column header and choose Merge Columns. We have set the Separator to Custom and entered the dash as our custom separator. We leave the name of the new column at the default of Merged, because one of our columns is already called Account. We can then rename our Merged column heading to Account after the Merge step is complete:

In order to allow our Excel Data Type to access our budget and actual columns, we will use the dropdown of the Use First Row as Headers option to select the antithetical Use Headers as First Row. We can then use Transpose, before resetting our headers using Use First Row as Headers:

Next, we can change our column data types to Text for our first two columns and Whole Number for all the others. We can change individual column data types by clicking on the data type icon at the left-hand side of the column header. To change the data types of multiple columns, select all the columns required using Control+Click or Shift+Click, then right-click in any of the selected column headings and choose Change Type:

Lastly, we select all of our columns and use the Transform Ribbon tab, Structured Column group, Create Data Type command to collapse all of our value columns into a single Account column:

This collapses our individual columns into a single, structured, Data Type column and we can then use Close & Load to load this single-column Table into an Excel worksheet:

When we load a query with a Data Type column to our worksheet, Excel recognises the data column and displays the Data Type symbol. We can then click in the column to display the list of available items and we just have to choose whichever of our actual or budget columns we want to display to add it to our Table:

In addition, we can refer to our Data Type column items from a cell outside our Table, and choose which of the available Data Types to use: