Add a Calculated Column to a Dataset

You can add a calculated column to any dataset that you have imported into a project. Calculated columns are retained in the repository even when you remove the dataset from the model.

Procedure

1. Open the model that contains the dataset you want to add a calculated column to.

2. Click the menu icon in the dataset header and select Create calculated column. The Create Calculated Column panel opens.

3. Enter a Display Name for the column.

4. Enter the Formula you want to use to calculate the column values.

   This must be a valid SQL expression that can be executed as an item in the SELECT list of a query. These formulas are passed directly to the underlying data warehouse at query runtime, so it must be written in a syntax supported by your data warehouse.

   Note the following:

   • Only use scalar functions in the formula. Do not use aggregate functions or table-generating functions.
   • If any columns in the formula contain protected keywords, they must be in quotes. For example, instead of cast(date as varchar(20)) you should use cast("date" as varchar(20)). Be sure to use the quotation rules for your datawarehouse.

5. To validate your formula, click Test SQL Syntax.

note

Note: This button is not available if you use Google BigQuery.

• If the formula is valid, click Apply.
• If the formula is not valid, verify your formula and try again. See Troubleshoot Invalid Calculated Columns.

1. Open Data Preview to confirm the calculated values are correct. You can search by the column name.

note

Note: If your data warehouse is an instance of Google BigQuery, calculated columns do not appear in the dataset preview.