Many datasets include empty cells - either because data was not collected for these cells, or was lost or corrupted. However, empty cells can create a problem when using the dataset for calculations - for example, in time series forecasting. Fill the Blanks is an advanced mathematical function that fills in any missing values, calculating the correct values from other measures that do have a value for the same number combination.
Using Fill the Blanks
Click the Fill Blanks button from the Query ribbon (red highlight below) or from the context menu. A custom measure will be automatically added to the query.
Open the Fill Blanks drop down options to choose whether to add the new measure to the query along with the existing measure, or swap the existing measure for the new one.
In this example, the Fill Blanks calculation was used to add a new version of the Returns column, with the title "Filled Returns", and the missing data values filled:
Explanations
Applying Fill Blanks calculation to the query auto-generates an explanation in the Notes tool. This explanation describes how the blank values were filled; it contains the calculation name and the algorithm used.
To view the auto-generated explanation, enable Notes from the Design ribbon. The explanations can also be viewed downstream in presentations.
In the example below, the explanation describes how the Fill Blanks calculation was evaluated for the query:
