• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Power Query Custom Data Types

You are here: Home / Power Query / Power Query Custom Data Types
Power Query custom data types
October 1, 2020 by Mynda Treacy

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:

Power Query custom data types

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

Subscribe YouTube

Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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

Power Query Get Data from Web

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

Power Query Get Data from Web dialog box

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

Power Query Get Data from Web navigator

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

Power Query Custom Data Types

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

Power Query Custom Data Types

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

name query

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.

Power Query Custom Data Types card

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:

Power Query custom data types

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:

Formulas with Power Query Custom Data Types

Power Query Custom Data Types Limitations

  1. There’s currently no support for images.
 
  1. You can only build a PivotTable from fields visible in the table.
 
  1. 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:

    Excel VALUETOTEXT Function

    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:

    Excel conditional formatting

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

 
  1. 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:

    Power Query Custom Data Types limitations

  1. 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.
Power Query custom data types
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Power Query Posts

get started with power query

Get Started with Power Query

10x Productivity with Excel Power Query in 3 easy steps. Get data > Transform Data > Load Data = HOURS Saved!

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.


Category: Power Query
Previous Post:quickly create running totals in power queryQuickly Create Running Totals in Power Query
Next Post:Grouped Running Totals in Power Querygrouped running totals in power query

Reader Interactions

Comments

  1. Jimmy

    December 15, 2021 at 5:04 am

    Hi Mynda,
    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,
    Jimmy

    Reply
    • Philip Treacy

      December 15, 2021 at 11:35 am

      Hi Jimmy,

      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?

      Mynda

      Reply
  2. NIRAJ TIWARI

    September 11, 2021 at 3:03 am

    Dear Madam,
    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.

    Regards
    .

    Reply
    • Mynda Treacy

      September 11, 2021 at 12:35 pm

      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.

      Reply
  3. Cedric McKeever

    March 25, 2021 at 5:58 am

    “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?

    Reply
    • Mynda Treacy

      March 25, 2021 at 10:01 am

      Hi Cedric,

      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.

      Mynda

      Reply
  4. Omar Radwan

    October 4, 2020 at 7:02 pm

    Hi Mynda,
    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.

    Thanks alot
    omarradwan@kfshrc.edu.sa

    Reply
    • Mynda Treacy

      October 4, 2020 at 8:42 pm

      Hi Omar,

      Please post your question and the URL you’re trying to access on our forum where we can help you further.

      Thanks,

      Mynda
       

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x