Open navigation

How to convert null values to zero in Data Studio reports

Replacing a “null” value with a 0 makes it easier to use math functions in your Data Studio reports.


You can also convert values to cosmetic zero, if you want to change their appearance in your reports but don’t need to use them for functions.


Instructions

There are 2 ways to convert “null” values to 0: a formula, or the NARY_MAX function.


Convert values with a formula

The formula CASE WHEN <yourfieldname> IS NULL THEN 0 ELSE <yourfieldname> END will convert “null” values. 


Follow these instructions to create a field with a formula.

  1. Open your report in Data Studio.
  2. Click any chart or table to open Supermetrics.
  3. Click the Metric field.
  4. Click Create field
  5. Enter your new field’s information, with the formula above in the Formula field.
  6. Click Apply

Here’s an example of what this looks like in the Facebook Ads “Comments count” field.




Convert values with the NARY_MAX function

The NARY_MAX function offers another way to convert these values.


To implement it, follow the steps above, but enter the function NARY_MAX(<yourfieldname>,0) instead of the conversion formula.


Here’s a Facebook Ads example with the “Cost” metric.



Cosmetic zero

To convert a value to "0" for cosmetic reasons, you can use a Data Studio setting to style the results to any value that you want to represent “no results”.


Open your report and click the element you’d like to adjust. Navigate to the Style tab and scroll down to Missing data. Then, choose your preferred value from the dropdown.


Troubleshooting

If any value in the math is null, it will turn the whole result null when you’re using metrics to create custom calculations for your reports. 


To solve this problem, you can create a new calculated field for each metric with the above formulas, and then use these fields instead to build your custom calculations. With the nulls as zeros, the math will be calculated correctly.


In some cases, especially with unique count metrics like Reach and Views, it might be that the report is trying to sum deduplicated values. If this is the case, follow these instructions to resolve the issue.






Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.