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.
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
-
To import the data from the web, select Data > From Web.
-
In the URL box, enter "https://en.wikipedia.org/wiki/List_of_presidents_of_the_United_States", and then select OK.
-
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).
Phase 2: shape your data
-
The Power Query Editor opens with the data you selected.
-
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 Transform > Create Data Type, which opens the Create Data Type dialog box.
-
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.
-
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.
-
Optionally, select Advanced to see all available columns, re-select which are displayed, and re-arrange the sequence of those selected columns.
-
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.
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
-
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
-
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
Create a nested data type
You can also create data types that contain data types, in case you need to organize your data in multiple levels:
-
Create a data type using the Power Query Editor, as explained previously.
-
Repeat the same steps, but now, when you open the Create Data Type dialog box, select the previously created data type in the Display column:
-
Select OK.
The result will be a data type that contains the previous data type you created.
To add the data type to an Excel table on a worksheet, select Home > Close & Load.
Recall: Using a Power Query data type is only supported in an Excel table.
Now, you can view the sub-data type by selecting it from the card: 
You can go back to the top-level data type by pressing the back arrow in the top corner: 
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)
