Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

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 define your data type in a later stage, it will require an item in your applied steps, creating unnecessary clutter. To get a clear 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.

Table of contents
Define Data Type While Adding Custom Column in Power Query

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, the 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.

Repeated steps in applied steps menu

Assigning data type to Custom Column

Imagine having the below numbers in Power Query.

Dataset

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
Add custom column multiplying values by two

The formula bar shows the formula:

= Table.AddColumn( 
      #"Changed Type",
     "DoubleNumbers", 
     each [Numbers] * 2
 )
Column showing the 'any' data type

When you create this custom column, the first three arguments of the Table.AddColumn function is filled. With that syntax, Power Query assigns the data type ‘any’ to the column, as indicated in the 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 4th 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 the fourth argument.

Right before the closing parenthesis:

  • Add Int64.Type -> press Enter
Manually change the M code in the formula bar

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

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

Data TypeSyntax 1Syntax 2
Whole NumberInt64.Type
Decimal NumberNumber.Typetype number
DatesDate.Typetype date
TextText.Typetype 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

Enjoy Power Query!

Share on:

Latest from my blog

  1. Rick, would it be possible to apply the single step data type definition while converting from a list to a table (say it is just a one-column table of dates), or do we need another step like a Renamed Columns to roll the type definition into it?

    Table.FromList(
        Dates,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
    )
    Reply
    • Hey Kirill,

      That’s a great question. You can provide a custom table type to provide the desired data type and name in the same step. You can do that in the third argument. For some examples, have a look at : https://powerquery.how/table-fromlist/

      Table.FromList(
        Dates,
        Splitter.SplitByNothing(),
        type table [Date = Date.Type ],
        Null,
        ExtraValues.Error
      )
      Reply
  2. This is only the latest blog read, usually enjoy your lists and table guides and List.Generate, but decided to leave a comment for a change. While waiting for a query to load, just searched syntax differences for data types, as Excel 365 and Power BI don’t seem to populate Syntax2 (type number), but do for Syntax1 (Number.Type) in bad intellisense.

    Best M info I’ve found anywhere! Keep up the great work for those of us who use Power Query and need documentation sometimes, or just fun reading material.

    Reply
  3. Thank you for this very help breakdown of the function’s 4 arguments, Rick.

    One thing that I can’t figure out. I’m using Office 365 Power Query. When I tried your approach, I don’t get any errors and everything seems to work functionality-wise. However, my query won’t return any values once I step out of the Power Query editor. Also, I don’t see that my values are being converted to numbers while viewing in the Power Query editor.

    Here’s my syntax:

    Table.AddColumn(
      #"Filtered Rows", 
      "UNIQUE_ID", 
      each Text.AfterDelimiter([Tracking ID],"-"), 
    Int64.Type
    )

    Thank you for any assistance you might be able to provide.

    Reply
    • Hi Jake,

      In your formula you are returning the Int64.Type, even though you extract text. Could you try this instead:

      Table.AddColumn(
         #”Filtered Rows”, 
         "UNIQUE_ID", 
         each Number.From( Text.AfterDelimiter([Tracking ID], "-" ) ), 
         Int64.Type
      )
      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.