FILL_NA

Overview

Description	Fills missing values (N/A) on existing level combinations after the specified dimension is pivoted into columns. Use this when reshaping or pivoting creates missing cells and you want to explicitly fill those gaps with a specific value.
Syntax	`FILL_NA('Node' [, "Dimension" [, Value]])`
Parameters	Node: Node that may contain missing values (N/A). Dimension (optional): Name of the dimension to pivot into columns before filling. Defaults to `"Time"` at the node's finest level. Value (optional): Value written into former missing cells on existing level combinations. Defaults to `0`.
Limitations	The node must already contain levels of the specified dimension. The function fills only where the dimension is pivoted into columns. It does not fill the complete time horizon and does not create missing level values that are not present in the input node (e.g., absent products). To fill missing values use FILLMISSING or FILLMISSING_LAST Linked levels of the chosen dimension also appear as columns in the pivoted result.

Examples

Filling missing time values with 0 (default)

This example fills missing values after pivoting the time dimension into columns. The default fill value is 0.

The time horizon of the project is 2025-2028.

The dimension "ProductDimension" consists of the level "Product" and the level values "Car", "Van", and "Truck".

The input node features the level values "2026", "2028" and "Car", "Van".

Input node: Input node

Year	Product	Value
2026	Car	100
2028	Van	90

Pivoted Input node by dimension "Time"

Product	2026	2028
Car	100	N/A
Van	N/A	90

Formula: FILL_NA('Input node') = FILL_NA('Input node', "Time", 0)

Product	2026	2028
Car	100	0
Van	0	90

This function fills the missing level values of the finest time dimension with 0. Notice that the formula does not fill the complete time horizon (e.g. expands the years "2025" and "2027").

Filling along a custom dimension with a custom value

This example fills missing values along the ProductDimension using 16 as the fill value instead of the default 0.

Formula: FILL_NA('Input node', "ProductDimension", 16)

Year	Car	Van
2026	100	16
2028	16	90

This function fills the missing level values of the dimension "ProductDimension" with 16. Notice that the formula does not add any values available in the dimension that are not present in the input (e.g. "Truck").

Related Functions

Function	When to use instead
FILLMISSING	When you want to add missing level values for a dimension, not just fill N/A cells from pivoting.
FILLMISSING_LAST	When you want to fill missing values using the last available value instead of a constant.