How to Add New Blank or Calculated Columns in Alteryx

Alteryx is a flexible tool that allows people to add columns to their data in numerous ways. They can create a blank new column to give extra space in their data set, calculate columns based on formulas, or bring in columns from completely different dataset.

Alteryx allows users to create new columns with the formula tool or add columns from other data sets by joining or appending

Working with columns in Alteryx is an important skill to master, however there is some nuance with Alteryx that doesn’t exist in other platforms like Excel or Power BI that involves first understanding the different data types which control what types of calculations and columns you’re allowed to create.

Let’s jump in!

The Role of Data Types in Alteryx

Data types play an important role in Alteryx. The software allows you a lot of granular control with many data types available, but generally data types define a column as text, numeric, or dates. This determines what actions you can take with a column.

For example, you can add two numeric columns together to produce the sum of two values. If you add two text columns together, Alteryx will concatenate the values into one long string or sentence and if you try to add text to a numeric column the result will be an error when trying to run the workflow.

Data type mis-matches are one of the most common errors that people will see when working in Alteryx and creating new columns.

Creating Columns in Alteryx with the Formula Tool

The formula tool can be used both to create new empty columns and calculated columns in Alteryx. To access the formula tool, go to the Preparation section of the Alteryx Toolbar and drag and drop it into your workflow. Once connected to incoming data, you can build formulas to create or modify columns.

Screenshot of adding a formula tool into a workflow to add new columns

When you click on the formula tool that’s been added to a workflow the configuration panel on the left side of the screen will show you a blank formula box.

Click the “Select Column” dropdown menu at the top of the formula configuration, and select “+ Add Column”

Using the Select column dropdown to Add a Column in an Alteryx formula tool

It might first appear that nothing happened, but your cursor will be blinking where the “Select Column” text used to be. This is where you can type in the desired name of your new column.

Naming a new column about to be created with an Alteryx formula tool

As an alternative to creating a new column, you could also select the name of an existing column which will update the existing column with whatever formula you enter below. This will be a welcome change for anybody coming from the copy and paste column world of Excel.

Creating New Blank Columns in Alteryx

To create a new blank column in Alteryx, add a Formula tool to your workflow from the Preparation section of the Alteryx Toolbar. Select “Add New Column” and name it. In the formula box below type two double quotes, “” to create a column of blanks or use the formula null() to create a column of null values.

Creating a new blank column in Alteryx using a null() formula tool

New columns are created on the far right of a dataset when they come out of the formula tool.

Creating Calculated Columns in Alteryx

Calculated columns can be created in Alteryx by using the Formula tool. Drag and drop it into your workflow, connect it to incoming data and select it. Choose “Add New Column” from the dropdown box, name the column. You’ll then be able to enter in a formula down below that creates calculations based on other columns.

In the example below, we entered a formula to calculate 1 year from the original date column.

Creating a calculated column in Alteryx by adding 1 year to an existing date column

A few things you should know when writing formulas to add calculated columns n Alteryx:

  • Text is defined with “Quote” marks around it.
  • Numbers do not have quotes around them.
  • Column names are surrounded by [Square Brackets]
  • You can begin typing a left square bracket and Alteryx will suggest existing column names
  • Not all functions are available for all data types
  • Don’t forget to define the Data Type of the newly created column.

Once you click off of the Formula Tool, your formula will automatically be saved and a new column will be added to the far right of your data set once it runs.

Adding Columns from Other Data Sources

Columns are added to a data set from another data source using the Join tool. The Join tool acts similar to a VLOOKUP in Excel and exactly the same as a SQL Join. It uses a common value in the original data set and the secondary one to match values by the contents of each row.

By default the Join tool will bring in all columns from the secondary data set, unless you unselect specific columns that you do not want to bring in.

Using an Alteryx Join tool to blend two data sources together and bring in a column from another file

Another option to add columns in Alteryx from a different data source is the Append tool. The append tool works differently than Join because it does not require you to have any common values between the two datasets. It will add a column to the far right of your initial data but be careful because it is also capable of duplicating rows when the values in the secondary data set change.

Using an append tool to add a column from another dataset in Alteryx when there is no common value present

Also be aware that data types play a role when joining data. It’s also a very common error to get when your data types of key columns don’t match. However, it’s a quick fix you just have to match the data types of the incoming data into a join tool.

Defining Data Types with New Columns

Pay attention to the data type that the formula tool is assigning to your new column. This can cause errors when attempting to run the workflow or create issues when trying to apply calculations that include the new values in the column that was just created.

Example of creating a new column in Alteryx and defining the outgoing data type.

As a general rule, we default numbers to “Double” and text to “V_WString” but there is a great deal of nuance between the different data types available, we recommend referencing the Alteryx Data Types Guide to learn more about what each one does.

How to Move and Re-order Columns in Alteryx

To move and change the order of columns in Alteryx, use the Select Tool from the Preparation section. Select a column you want to move and use the Up or Down arrows at the top of the configuration panel to change their order. Columns listed top to bottom will appear left to right in your workflow results.

Using the Alteryx Select tool to move column order

For more comprehensive movements, you can hold the right-mouse button where the blank box is to the left of the field name and drag and drop columns into a new order.

The select tool also lets you change the data types of columns which may be important before feeding data into a formula tool for a calculation.

Conclusion

Creating new columns in Alteryx is quick and easy using the formula tool. New columns can be created that are empty, contain a simple string of text, or have more complex formulas to calculate values based on the values in other columns.

Regardless of which type of column you are creating, don’t forget to update the data type of the new formula and ensure that the data types are correct going into a formula tool to avoid calculation errors at runtime.

The Join Tool or the Append Tool can be used to bring in columns from other data sets. The Join tool will match up values based on common criteria while the Append tool is value agnostic.

Scroll to Top