Power Query Data Types [The Game Changer]

Welcome to Excel Jet Consult blog post. In this blog post, we will look at how to use the newly released Power Query Data Types. Note, as at the time of writing this post, the PQ Data Types is currently available to Excel for Microsoft 365 E3 and E5.

Sample Data

We have sales data in the notepad of the caption below. Essentially, we will connect to the notepad source data to import the records into the Power Query editor before we deploy the data types

Import Data

To import the data from text:

  • In the Get & Transform Data group of the Data tab, click on From Text/CSV

  • Select the File from the stored location and click on Import. The Text value is named Sales Data

  • In the intermediate dialogue box as seen below, click on Transform Data

In the Power Query Editor, we can see we have established connection to the text file.

Create PQ Data Types

Next, we want to create Power Query data types based on our dataset.

To do that,

  • Scroll to the right of the screen using the horizontal scroll bar where we have the last column which is Total.
  • Hold down the shift key and select Total column. All the columns becomes selected
  • In the Structured Column of the Transform tab (far right of the screen), select Create Data Type. This will collapse all the selected columns into a single column data type.
  • In the Create Data Type column, we provided, for this example, SalesRecords as Data Type Name and selected Sales Manager as display column

  • Click OK

From the caption below, we can see that all the selected columns is collapsed into one.

  • To load the transformed data back to Excel, in the Home tab, select Close & Load To…
  • In the Import Data dialogue box, we are fine with Tables and New Worksheet. 

  • Click OK

Data Type Consumption

Finally, we can consume the data in Excel. To add a new column, click on the Add Column icon to the right of the cell and select any column to add

In the caption below, we can see all available columns to choose from

When we select, for instance, State column, all the records in the State column will be added in column B as seen in the caption below

We can also type in the first few letters of the column that we want to add. For instance, we want to add Customer Type column, in cell C1, I typed cust and clicked Enter, all the records for Customer Type are available in column C as seen in the caption below

We can also use formula to add a column. To do that, type = and select cell A2 which will show =[@SalesRecords]. Then, from the dropdown seen in the caption below, select the column to add


We have seen how to use the new Power Query Data Type to handle large and small datasets. This is really a change changer! Do watch the video

Posted in Blog and tagged .

Leave a Reply

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