Power Query Custom Data Types

You may already be familiar with Excel Data Types for geography and stocks, but with Power Query Custom Data types we can now create data types based on our own data.

This enables us to organise our data into a single column and then extract and reference the underlying columns/fields using formulas. It’s a streamlined way to manage and consume your data enabling you to create interactive reports like the one below:

Watch the Video

Download Workbook

How to Create Power Query Custom Data Types

In this example I’m going to get some data from the web for 2020 Tour de France from this URL:

https://en.wikipedia.org/wiki/List_of_teams_and_cyclists_in_the_2020_Tour_de_France

Step 1: Excel Data Tab > From Web

Step 2: Enter the URL for the website > click OK

Step 3: Select the Table you want to import > click Transform Data

Step 4: Select Columns to be included in your data type > Transform tab > Create Data Type

Tip: before you create your data type, use the filters to remove any unwanted data, rename any columns as necessary and set the data types for each column e.g. dates, text, numbers etc.

Step 5: Give your data type a name and choose which column you want displayed

Step 6: Rename the query if required and Close & Load to a Table

Tip: the query name will be the name of your Table when it’s loaded to the Excel sheet and you’ll use this name when referencing the data, so make sure it’s something useful.

You should now see your data type in the Excel worksheet ready to use. Click the data type symbol beside a rider’s name to see the underlying data for that record.

Or click the Card icon to reveal the fields available to work with. Click on one of the fields in the list to add it to the table, or type the field name in the header, or reference it in a formula as shown below:

Note: the beauty of the data types is that you don’t need the columns displayed in the table to work with them (unless you want to use them in a PivotTable). With Data Types you can reference the fields in formulas:

Power Query Custom Data Types Limitations

1. There’s currently no support for images.

2. You can only build a PivotTable from fields visible in the table.

3. Values with a Data Type icon are not the same as text, as you can see in the image below when I compare the data in cells D2 and E2 to the rider value in A2. However, we can convert data types to text using the new VALUETOTEXT function as you can see in cell D6:
   

   

   This is useful when looking up text values in data type columns. e.g.

   =XLOOKUP(E2,VALUETOTEXT(Riders[Rider),Riders[Rider].Number)

   And in the Conditional Formatting in my example file:

   

   Another function designed to work with data types is ARRAYTOTEXT, which converts the array to a comma delimited string of values.

4. COUNTIF/S, SUMIF/S etc, cannot handle the array returned by Riders[Rider].Team e.g. this formula will not work because COUNTIF requires a range in the first argument:

   =COUNTIF(Riders[Rider].Team,C3.Team)

   However, this equivalent of the COUNTIF formula using SUM and Boolean logic will work because SUM can handle an array:

   =SUM(--(Riders[Rider].Team=C3.Team))

   The point being that some functions can handle the arrays returned from data types and some can’t. You can use workarounds like the alternate SUM formula, or you can perform the calculation in two steps; 1. return the data to cells and then 2. reference those cells in your formulas. E.g. =COUNTIF(K4#,C3.Team) where K4# is the range returned by =Riders[Rider].Team as shown below:

   

5. Not so much a limitation, but you’d think with only one column of data occupying cells in the worksheet that the file size would be smaller than if all the columns were visible. However, in my experiments the file containing the data type was slightly bigger than all the data stored in a regular table without a data type. So, while data types won’t reduce your file size, they sure make your workbooks less cluttered.