Create a Data Type

Introduction

I am assuming here that you know Power Query both in Excel and in Power BI. I also assume that you know what is meant by the term Data Type and you know how to use Data Types.

This page concerns the creation of our own Data Type in Excel. Thankfully this is not a difficult task and I hope my explanations do not make the task difficult.

You can download a file of mine from the link at the end of this page: speedtest_sep_2020.xlsx and it will open in google docs in a new window. That file contains all of the data I have created over a number of years relating to my internet/wifi connection. I use the speedtest software and ask it to check my location, the date and time, the type of connection, the ISP/Server I am connected to, my download and upload speeds and the latency of that connection. This file contains about 1715 data points and I create the file so that I can demonstrate Power Query fairly readily since I know and understand the data in full and I am constantly adding to it.

Get Data

Start by getting data: the speedtest file in this example. At this stage, there is nothing you don't know: ordinary, everyday get data. As you get it into Power Query, choose Transform Data to open the file in the Query Editor.

Query Editor

Edit and tidy the file in the Query Editor to suit your needs: change data types, set the region for dates and times if necessary, move columns around, add columns and so on.

Create Data Type

In my case, I selected all columns since I want all of my data to be included in my Data Type: solve any problems and deal with any errors before you create your data type.

Click column 1 header, press and hold the Shift key then press the header of the final column. All of your columns are now selected. 

Transform Ribbon ... Create Data Type

That's it! You will now see something like this:

You probably want to change the name of this Query and, having done that, Close & Load it.

Done!

Using your Data Type

Now what? What do we do with our new Data Type? I said at the start of this page that I assume you know how to use Data Types already. However, let me give you a couple of hints.

I have already entered some data from my new Data Type, columns B:F. To enter more, I click on the Add Column icon you can see at the top left of column G and choose the additional data you want to add.

I can see here all of the column headings from my Data Type and if I click Latency, it will add the Latency column to my table. That is it! So simple.

Use Formulas

We can use formulas to get data from my Data Type into my table and I will discuss them in my next Blog page, Data Type Formulas

Download my speedtest data file: 

Duncan Williamson

17th April 2021