Custom data types

Traditional Excel has only two data types: text and numbers. All others (date, time, percentage, etc.) are specially formatted numbers. In the second half of 2019, Microsoft added new data types to Excel: Geography and Stocks. First, for a given term, offers an abundance of geographical determinants while the second provides data on stock market. A lot of new types have been announced, and since recently users of Microsoft 365 subscriptions can create custom data types.

To demonstrate this functionality, I downloaded a list of chemical elements from the periodic table from:

I imported it into Power Query (Data/Get Data/From Other Sources/From Web) and then performed a few simple transformations to get a detailed overview of the chemical elements. Each row in the table represents a separate chemical element, and its characteristics are described in the columns: atomic number, symbol, name of the element, etymology of the name, group, period, etc.

Before creating a new data type, you must first select all columns. Then in the Transform ribbon you need to run the Create Data Type option.

A window will open in which you must first name the new data type (Data Type Name) and then in a dropdown list select the column from the previously retrieved and transformed table, which will display the basic data about it (Display Column).

After confirming the entry, a table of elements will appear, represented by the column we previously selected. If we click on it, you will see a short description at the bottom of the screen, which is a summary of other columns that will be available when working with a new data type.

If we close the Query Editor (Close & Load option) Excel will automatically create a new worksheet that displays a list of chemical elements. As with the Geography and Stocks types, by clicking on the appropriate icon, to the right of the one to open the filter drop-down menu, it is possible to add a new column containing some of the data that is part of the original query. In this way, in addition to the symbols, we can add a name, atomic weight, group, etc.

Custom data types can be very useful. Their business application is based on the ability to retrieve important data to the organization in Excel as new data types. For example, if we import a table with personnel data based on the name and surname of the employee, we will be able to access the date of birth, date of employment, years of work experience, education etc.