Column Transformations#

Grist offers two ways to transform all values in a column. One is to change the type of the column, and the other is to apply a formula-based transformation.

Type conversions#

When converting between different column types, Grist has sensible default behavior, but makes that behavior easy to revise. For example, suppose you have a column of integers. To convert that column to text, open the column options as described in Specifying a type, and find the column type section. Change the column type to text in the dropdown. You’ll notice that a “cancel/revise/apply” dialog opens beside the dropdown.

To change how the conversion is done, click Revise. You’ll see a formula box with the default conversion method, grist.Text.typeConvert($tally). This means “do default conversion to text for the tally column”. you can replace this with any formula you like. For example:

Code for converting to unicode tally lines is left as an exercise to the reader. To preview the results of the conversion, click “preview”. When you are satisfied with the conversion, click “apply”. To abandon the conversion, click “cancel”.

Formula-based transforms#

Spreadsheets are convenient tools for cleaning up data using formulas. For example, imagine you had zip codes that have lost leading zeros - you can easily reformat them with a quick formula:

We could now freeze the results and delete the original data if we don’t need it anymore.

If you know you’re going to throw away the original data like this, Grist offers column transformations as a faster way to systematically modify all cells of a column. Find the “Transform” section at the bottom of the column options side panel (see Columns for how to open this panel).

When you click the orange “lightning” button, Grist prompts you with a formula, return $zip in this case. You can edit this formula to make some change to the selected column. For example return $zip + 1 would add one to the zip code. You can preview the effect your formula would have, and when you are happy, hit “Apply”. In our case, where we want to add leading zeros, we’ll need to first change our column type to be Text (assuming it is currently Integer - if it Numeric convert to Integer first and then to Text to avoid decimal points). Once done, we can use our formula for adding leading zeros:

When happy, press “Apply” to replace the cell values with their new versions. Likewise, the response column could be transformed with the formula into true/false values with $response[0] == 'y', and then set as a toggle column.