Define Data Type While Creating Custom Column in Power Query

In this post you learn how to define a Data Type at the same moment as you add a custom column. By default, Power Query creates a custom column without specifying the data type. When you then define your data type in a later stage, it will require an items in your applied steps, creating unnecessary clutter. To get a clean overview it is beneficial to structure your query without unnecessary steps. So how can you create a custom column and define the data type in that same formula?

You can find the video tutorial at the top of this article. If you would rather read the tutorial or need more instructions, then please continue reading.

Clutter by Applied Steps

When you add a custom column, by default you can’t assign a data type to it. Yet when you change the data type after creating the column, a separate step is created. To illustrate below picture shows what your query may look like. As you can see it contains many ‘Change type’ steps. Wouldn’t it be great if you could get rid of all those steps, but still assign the correct data type? The good news is, you can.

Assigning data type to Custom Column

Imagine having below numbers in Power Query.

You want to create a column that multiplies the numbers by 2. Let’s start by adding a custom column:

• Go to Add Column -> Click Custom Column
• Name the column DoubleNumbers -> fill in formula = [Numbers] * 2
• Click OK

The formula bar shows the formula:

= Table.AddColumn( 
      #"Changed Type",
     "DoubleNumbers", 
     each [Numbers] * 2
 )

When you create this custom column, the first three arguments of the Table.AddColumn function are filled. With that syntax Power Query assigns the data type any to the column, as indicated in above picture.

Yet the Table.AddColumn also has a fourth optional column. In this column you can provide a data type to the column. To do that, you need to adjust the M-code.

Adjusting M-code

The arguments for the Table.AddColumn function are:

= Table.AddColumn(
     table               (as table), 
     newColumnName       (as text), 
     columnGenerator     (as function), 
     optional columnType (as nullable type)
 ) as table

The 4^th argument has an ‘optional columnType’ as input. You can leverage that argument to assign the correct Data type to the column. In the new column you find only whole numbers. To change the Data Type to a whole number, you can add Int64.Type as fourth argument.

Right before the closing parenthesis:

• Add Int64.Type -> press Enter

And there you have it. The Data Type of the column has now changed to the right data type, but this time within the same step.

Adjusting the M-code may seem scary at first. Yet as this example shows, it is not that difficult. In the end adding the definition of a Data Type to the formula can save you many steps when adding new columns.

Syntax for Different Data Types

Below table shows the available syntaxes with the Power Query data types. Previous example uses Syntax 1. However, you can replace Syntax 1 by Syntax 2 for similar results. Just insert the syntax at the position described earlier.