Create a data type (Power Query) (2024)

Excel has anumber 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

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).

   

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.

   See Also

   Data types in Data ModelsEnter data manually in worksheet cellsThe Types of Cell ElementsConvert a data type - Microsoft Support

   

   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 Advancedto 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 arecollapsed 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

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

   The data type displays theInsert Databutton

   

   which you use to work with each column.

   

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 Databutton

   

   :

   

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

   

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

   

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

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:

1. Create a data type using the Power Query Editor, as explained previously.

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

   

   You can give the new data type any name you like; in this example, it’s “President_2”.

3. 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 addimages 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