Data munging tip #1: Adding a column with staticColumn

Canvas expressions have a few ways of adding a column to a data table. In this post we'll look at the simplest: staticColumn. This function is simple, effective and fast at add static values and, when combined with a sub-expression, summary columns. In these examples we'll use the demodata datasource to keep things simple, but of course these all work with your data too.

A simple example that would add a column called hello containing the string "Hello World" would look like this:

demodata | staticColumn hello value="Hello world"

Simple right? The unnamed parameter is the name of the new column to add, and the value is "Hello world". However, we can leverage what we know about sub-expressions to do so much more. Remember, sub-expression receive the same context as the primary function. For example:

demodata | staticColumn total_sales value={math 'sum(price)'}

In this example, math receives the output of demodata, so we can use it to summarize the price column into a new column called total_sales. We could now use markdown to construct a simple metric element with a summary of our recent sales history. Again, remember that getCell will receive the datatable that has been output from staticColumn. Here were accessing the first row since the default for the row argument to getCell is 0:

demodata
 | staticColumn total_sales value={math 'round(sum(price))'}
 | markdown "We've sold **$" {getCell total_sales} "** worth of product.
   Our most recent sale was $" {getCell price}