Documentation Data Pipeline FAQs

Calculate row number in Data Explorer

If you joined after January 6, 2020, you might be using our new Visual SQL interface. Check out Calculate row number in Visual SQL for more relevant content.

It can often be handy to have row numbers when viewing data in tabular form, to display information or to use in calculations.

Let’s take a look at how we can calculate row numbers in the Data Pipeline in order to generate a Table chart similar to the one below:

Table with rowid added

To add a column with the row numbers to your chart, open a chart using the Edit Chart Data option from the chart menu on your dashboard. Then, in the Data Pipeline, click the +Add Transformation or + button in the and click on Add Column.

Add the Add Column Pipeline step

Then, in your Pipeline step, select Custom formula as the Formula Type and enter the following as your custom formula in the Formula field:

rowid

Click Apply & Close and your new column will be added.

Use a custom formula in the Pipeline

Rowid column placement

If you want your new rowid column to be the first column in your chart, simply add a Reorder Columns Pipeline Step and move your new column to the top of the column list to be in position #1.

Reorder Columns in the Pipeline

Rowid order

Your rowid values will start at 1 and be calculated according to the order of your rows at the time you add the Add Column Pipeline step.

If you would like your rowid values to be populated based on a different row order, just add a Sort Rows Pipeline step before the Add Column Pipeline step to reorder your results.

Sort Rows in the Pipeline before the Add Column step

Once you do this, the Add Column step with your rowid formula will automatically be adjusted. Simply save your chart to save your changes.

Tip: You can use this rowid column in a number of ways, for example as part of a CASE statement to set conditions depending on the rowid value or in a Filter Rows in the pipeline to include or exclude some rows.