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
Conclusion
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