Excel has a number of linked data types that you can use right away. But, you can also create your own data type from any supported data source by using Power Query. First you import your data with Power Query, then you shape the data the way you want, and finally you load the data type into a worksheet so you can work with the data. Like all data types, you can view the data in an Excel table, display it as an easy-to-read card, or use it in formulas.

Your browser does not support video.

Note   Creating a new data type is supported in Microsoft 365. For more information about ensuring you have an up-to-date subscription, see Microsoft 365 for business subscription.

Create a data type by using the Power Query Editor

The following procedure uses an example based on a list of American presidents from Wikipedia.

Phase 1: import your data

  1. To import the data from the web, select Data > From Web.

  2. In the URL box, enter "https://en.wikipedia.org/wiki/List_of_presidents_of_the_United_States", and then select OK.

  3. In the left pane of the Navigator dialog box, select Presidents, which shows the data in Table View, and then select Transform Data (at the bottom, right-hand corner).

    Selecting the Presidents table and transforming it

Phase 2: shape your data

  1. The Power Query Editor opens with the data you selected.

  2. Select one or more columns. In this example, select President2, Party[b]2, Election, and Vice President.  The order of the column in the new data type is determined by the order of selection.

    Tip   To select any column, press Ctrl+Click. To select adjacent columns, press Shift+Click.

    Select columns to include in the data type

    Note   Power Query automatically displays the associated M formula in the formula box. For more information, see Power Query M formula language.

  3. Select Transform > Create Data Type, which opens the Create Data Type dialog box.

  4. To change the column header for the data type, in the Data type name box, enter "US President". This column header is also the name of the data type.

  5. To specify the display column for the data type, select Display Column. For example, select President2 which contains the President’s name.

    Note   The default display column is the first selected column.

  6. Optionally, select Advanced to see all available columns, re-select which are displayed, and re-arrange the sequence of those selected columns.

  7. When finished, select OK.

Results

The data type column with a data type icon and column header named "US President" is created. All the columns you initially selected are collapsed into this one column.

The data type column is created

Note   A new step is created in the Applied Steps section of the Query Settings pane. For more information, see Create Power Query formulas.

Phase 3: add the data type to an Excel table

  1. To add the data type to an Excel table on a worksheet, select Home > Close & Load. Using a Power Query data type is only supported in an Excel table.

    The data type displays the Insert Data button Add Column button  which you use to work with each column.

    Loading the new data type into an Excel table on a worksheet

  2. You can work with your new data type in various ways. For example:

    Extract one of the data type properties, such as Vice President, into a column in the table, by selecting the Insert Data button Add Column button:

    Adding a data type column

    View column information in a card, such as John Adams, by selecting the data type icon:

    Viewing data in card view

    Extract one of the data type properties, such as Election, into a column in the table, by using the equal sign (=) and Intellisense:

    Adding a column by using a formula

    For more information, see How to write formulas that reference data type.

For more information about working with data types, including how to refresh data and add images to a card, see Linked data types FAQ and tips.

More about creating data types

Import data from external data sources (Power Query)

About Power Query

Create Power Query formulas

How to use linked data types with Excel

Excel data types: Stocks and geography

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×