Define Data Type While Creating Custom Column in Power Query #9

Define Data Type while adding Custom Column -- Featured Image

When working with Power Query, it is easy to end up with many applied steps. And the more applied steps your query includes, the more clutter you find on the screen. To get a clean overview it is beneficial to structure your query without unnecessary steps. In this post you learn how to define a Data Type at the same moment as you add a custom column.

Video Tutorial

If you would rather read the tutorial or need more instructions, then please continue reading.

Clutter by Applied Steps

When you change Data Type after you add a custom 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. You can adjust this query easily to get the same results with 4 less Applied Steps.

Define Data Type while adding Custom Column - 1. Applied Steps

Imagine having below numbers in Power Query.

Define Data Type while adding Custom Column - 2. Data

Add Custom Column

This technique works right after you add a custom column. So 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

Define Data Type while adding Custom Column - 3. Add Column

Often when you create a custom column, Power Query provides it with the data type ‘any’. Just like in below picture. Also take a moment to note that currently the formula below does not contain the Data Type. To do that, you need to adjust the M-code.

Define Data Type while adding Custom Column - 5. Column Type Any

Adjusting M-code

Microsoft documents the arguments of the Table.AddColumn formula at this website. Below you can find its arguments.

Define Data Type while adding Custom Column - 7. Add Column Formula Arguments

The 4th argument has an ‘optional columnType’ as input. You can leverage that argument to change the newly created column to the right Data Type. In the new column you find only whole numbers. To change the Data Type to a whole number, all you do is add some code to the formula.

Right before the closing parenthesis:

  • Add “, Int64.Type” -> press Enter

Define Data Type while adding Custom Column - 6. Change M Code

And there you have it. The Data Type of the column has now changed to the right data type, 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 Data Types

Below table shows the available syntaxes to change a Data Type. 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.

Data Type Syntax 1 Syntax 2
Whole Number Int64.Type
Decimal Number Number.Type type number
Dates Date.Type type date
Text Text.Type type text
Binary type binary
Date/Time type datetime
Date/Time/Timezone type datetimezone
Duration type duration
Function type function
List type list
True/False type logical
Record type record
Any type any
Any Non-Null type anynonnull
None type none
Null type null
Type type type


Rick de Groot

My name is Rick and my goal is to help you advance with Excel and Power BI. I believe learning is one of the great pleasures in life and I want to share my knowledge with you to help you improve your skills.

1 Response

Share Your Thoughts!