Using Coalesce Transform

Missing values in your data can sometimes affect calculations, queries, reports, data-set planning, etc. To address the missing or null values, the Coalesce transform evaluates the arguments in the specified order and always returns the first non-null value from the argument list.

For example, you have a Name that you want populated with the last name, but if the last name is missing a value, you want the first name to be stored in the Name column. In this case, the transform would check for the last names to be put into the Name column, but if it finds a missing value, it will look for the first name and populate the data.

Creating Coalesce Column

  1. From the data tab in the metric builder, select the Add Transform button, then select Coalesce.

  2. Enter a name for the new column in New Column Name field.

  3. In the Column(s) field, select + Add column, to add a column from the existing columns in the dataset. You can add more columns in the same way. For the example discussed earlier, you can first add the Last Name column, then the First Name column.

    After you have selected the first column, you can only select other columns with the same data type for the coalesce function.

  4. Once you have added the columns to be considered for the transform, select Run and your new column will populate in the table of data.

  5. Select Save to save this report.


Was this article helpful?