LastNonNull

The LastNotNull function returns the last non-Null value in a column or grouping.

LastNonNull is either an aggregate function, or a window function. As a window function, it repeats the last non-Null value for every row in the column.

This is similar to the Last function, but excludes Null values.

Syntax

LastNonNull([Column])

The function has the following argument:

Column - The column input and accepts all data types

Examples

Window function example

LastNonNull([Delivery Date])

Here the calculation references a column in a lower grouping level. This example shows the oldest non-null date from the Delivery Date column for each grouping under the Order Number column.

Delivery Date is in descending order sort. If Delivery Date is in ascending sort, the function returns the most recent non-Null date for each grouping.

Aggregate function example

LastNonNull([Delivery Date])

Here the calculation references a column in the same grouping level, so the first non-Null value returns for every row.

Note how the resulting calculations depend on the group’s sort order. The sort on the Product Family affects the order of the Delivery Date column.