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:
Note: Power Query Custom Data Types are currently in preview on the Beta channel for Microsoft 365 Windows users, however only 50% of Beta channel users will have received this new feature. I just happened to be in the lucky 50%! When the feature is generally available it may be restricted to a specific licence, but I don’t have details on that yet.
Watch the Video
Enter your email address below to download the sample 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:
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
- There’s currently no support for images.
- You can only build a PivotTable from fields visible in the table.
- 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.
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.
- 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:
However, this equivalent of the COUNTIF formula using SUM and Boolean logic will work because SUM can handle an array:
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:
- 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.
Thanks for a great tutorial as always.
I have a bit of an issue with loading created Data Type from PQ into table in Excel. I can see the Data Type and all associated info below the table but when Load to Excel table there is just regular column without associated data to show.
I have deleted the whole query and made from scratch several times but the problem remains. Any ideas how to solve this?
Many thanks and BR,
I’ve not seen that problem before, sorry. It looks like you need a Microsoft 365 for Business subscription. You cannot use a personal subscription. Could this be the issue?
Your excel tutorial on youtube is so enriched with the knowledge it has enriched me in excel. Wish you knowledge sharing continues.
Madam, I have a query in power query, “How to separate date and text data from a single column in a set of data.
Glad it was helpful, Niraj! Regarding your date question, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
“Step 5: Give your data type a name and choose which column you want displayed”
This is probably a stupid question, but doesn’t the column you choose to display have to have unique values?
The column you choose to display doesn’t have to contain unique values, but it would make more sense if it did, otherwise you’ll have multiple values and won’t know what they represent without expanding the data.
Thanks for your training that I am always waiting to see for your new things to add to my value and expert.
I want to ask about the training for (NEW Power Query Custom Data Types), from the 1st Step when I go through the Data Tab>From Web and after I entered the given URL it didnt show me as what you are descriping, and when I try to figure it out to do it from another way I also failed.
It always import the entire page content from that URL and there is no option to chose from.
If its possible could you make a meeting with me through Microsoft Team (via my mentioned email) so I can share my disk content and you can see the problem in steps and discribe it for me.
Please post your question and the URL you’re trying to access on our forum where we can help you further.