When the new Geography and Stock data types debuted in 2018, I would frequently ask the audience at my Power Excel seminars for ideas of how to improve this feature. Many people asked for Exchange Rates, which ultimately was added in March 2019. Some asked for Weather. This was added in beta in August 2020 as the “Location” option with the new Wolfram Alpha data types.
A few people asked for the ability to create their own data types. This was a great idea. Imagine having your company’s products as a data type. You could store a SKU as a data type in cell A2 and use formulas to get =A2.Category, =A2.Color, or =A2.[List Price]. It sounded fantastic, but it seemed unlikely.
With an announcement in late October 2020, Microsoft implemented its first iteration of custom data types. Rather than requiring your IT department to create a database in XML, the Excel team created a very simple process using the Power Query tools found on the Data tab. This allows users of Office 365 on a Windows PC to define their own data types.
NOT QUITE COMPLETE
Once you define a data type in Power Query and load the data to the Excel grid, you can take advantage of most of the features of linked data types. Click on the icon in the cell to display the Data Card. Use formulas to extract other fields to the grid, such as by using =A2.Category or =FIELDVALUE(A2,“Category”). Since all data returned from Power Query is stored in a table, you can use the filter drop-down and choose to sort products by Category or filter by Category even if Category isn’t displayed in the grid.
Some important features are missing, however. For instance, there’s no way for the Data Card to display a picture of the item. This seems like it would have been easy by specifying “Image URL” like Power BI lets you do, but it isn’t available yet. Another problem is that there’s no easy interface to select a bunch of cells containing SKUs and ask Excel to convert those to your Product data type using a previously defined query in Power Query. Both of these are likely coming, but they aren’t part of the first release of custom data types.
SAMPLE USE CASE
Imagine you have a large sales data set. You might have 100,000 records of Date, Store ID, Customer ID, Product ID, and Quantity. The IT department stores a short Product ID rather than the full description, which leaves the accounting department using VLOOKUP (or XLOOKUP, or INDEX/MATCH) to bring the item description into its data set.
A modern approach is to define the Sales workbook as a Connection Only query in Power Query and then define the Product lookup table as a Connection Only query in Power Query. You can use a Merge query to join the data together.
I worked through a recent example where I pulled in a Product lookup table, a Customer lookup table, and a Store lookup table. The narrow data set quickly grew to 20 columns since I chose to include everything even though I wouldn’t need all of the columns from the lookup table most of the time.
In Power Query, select all of the columns related to Product. On the Transform tab in Power Query, choose Create Data Type. Rather than displaying the Product ID code, you can choose to show the Product Description in the column. This is far more useful than a bunch of product numbers. Remember that the person using the worksheet can easily add Product ID later using a formula or by simply displaying the Data Card.
Here, the original CustomerID, StoreID, and ProductID fields have been replaced with Customer, Store, and Product Description:
You can click on any icon in columns C, D, or E to display a Data Card with additional information about the customer, store, or product.
Other subtle things are happening as well. It’s easy to filter the Product Description field by author even though author isn’t shown in the grid. In column F, a formula such as =[@Products].Price brings a field from the Data Card into the grid. And in column G, a formula multiplies Quantity from the original Sales data by Price from the Data Card.
The advantage is that people using the spreadsheet start out with a narrow spreadsheet and, using simple formulas, reveal only the lookup columns that they need.
SF SAYS
With the framework for custom data types done, hopefully the Excel team will add support for images and reusability within a workbook.
December 2020
