# Getting the First Record with a Non-Null Value in a Pivot Column or Pivot Row

• jeffrey.martinez • Looker Staff
• 37 replies

Looker will not be updating this content, nor guarantees that everything is up-to-date.

Pivoting results by a dimension in an Explore is a great way to organize results and visualizations in a more readable way. Sometimes, however, pivoted results can display quite a few null values in a column where there are no matching values for a row:

So what do we do if we don't want to have to wade through that sea of nulls just to find the first non-null value in a pivot column or row? This article discusses how to find that first non-null value by using table calculations.

# Getting the First Non-Null Record in a Pivot Column

The following table calculation will return the first non-null value of a pivot column as a single constant value:

``index(\${history.query_run_count},min(if(NOT is_null(\${history.query_run_count}), row(), null)))` `

Here's how it works:

1. ``if(NOT is_null(\${history.query_run_count}), row(), null)``

In this statement, the if() function evaluates each row one at a time. If the measure value in a given row is NOT NULL (`NOT is_null()`) then we output the cell's row number with the row() function. If the measure value is null, then the calculation outputs null.

2. ``min(if(NOT is_null(\${history.query_run_count}), row(), null))``

We take the minimum of the list values provided in step 1 with the min() function, which outputs the lowest (first) row number in that column that has a non-null value.

Note: if we wanted to find the last non-null value, we could simply change this step to use max().
3. ``index(\${history.query_run_count}, min(if(NOT is_null(\${history.query_run_count}), row(), null)))``

The index() function returns the value of the nth element of the column created by an expression, unless an expression defines a column of lists. If an expression defines a column of lists, `index()` returns the nth element of each list. Since we have already calculated which row has the first non-null value in steps 1 and 2, we plug the numerical value of `min(if(NOT is_null(\${history.query_run_count}), row(), null))` in for our value n, and now we have the first non-null value in our pivot column.

In a results table, the table calculation looks like this:

# Getting the First Non-Null Record in a Pivot Row

The calculation below returns the first non-null value of a pivot row. The calculation will appear as a column to the far right of the pivoted results:

`  `pivot_where(min(pivot_row(if(NOT is_null(\${history.query_run_count}), pivot_column(), null))) = pivot_column(), \${history.query_run_count})``

Here's how it works:

1. ``if(NOT is_null(\${history.query_run_count}), pivot_column(), null)``

Similar to the solution above, this `if()` statement evaluates each row one at a time. If a measure value is NOT NULL, then it outputs the cell's pivot column index with the pivot_column() function. If the measure is null, the output is null.

Note: In Looker, pivot columns are numbered left to right starting from 1.
2. ``min(pivot_row(if(NOT is_null(\${history.query_run_count}), pivot_column(), null)))``

`pivot_row()` makes a row-wise list of the values from step 1. This is an important step, because this makes sure that the `min()` is taking the row minimum, not the column minimum. This minimum gives us the lowest (first) pivot_column in that row that has a non-null value.

Note: If we wanted to find the last non-null value, we could simply change this step to `max()`.
3. ``pivot_where(min(pivot_row(if(NOT is_null(\${history.query_run_count}), pivot_column(), null))) = pivot_column(), \${history.query_run_count})``

The pivot_where() function returns a list of the values from the pivot column that satisfies an expression, or null if such a column does not exist.

Since we have already calculated which pivot column has the first non-null value for each row in steps 1 and 2, we need to specify a condition that points to the value in that row. We do this by checking when our current pivot column is equal to the value from steps 1 and 2. Where that expression is true, the calculation outputs the value of the measure.

In a results table, the calculation looks like this: