FILL_NA
Category: Filtering & data shaping
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 |
|
Parameters |
|
Limitations |
|
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. |