Null values are very common when you attempt to blend data from different data sources. This article will show you how to turn those null values into zero in a numeric format so you can mathematically operate them.
For whatever reason, you may have attempted blending data from different sources and expected your numeric columns to return numbers that could be mathematically operated.
But it kept returning null for some reason, which can sometimes leave you frustrated and furious.
Here, we’ll show you how we deal with null responses when we blend data – especially when we are sure that our combinations are faultless.
We need those null values shown in text or strings data format to be returned in numeric formats to either get a grand total value or use the numbers for more arithmetic expressions.
Check out our free templates gallery
So here’s what you should do when you see that null value, and you need to change that to a number or zero. The formula often used for this is known as the NARY_MAX formula and is expressed as
SUM(Column name) + SUM(NARY_MAX(Column name, 0))
The formula above can be copied and pasted on your Looker Studio Report under calculated fields.
All you will need to do after blending your data sources would be to replace the column names with the actual names of the columns you need to be changed.
First, you must blend your two data sources using the left join or any join parameter favorable to you. To learn how to blend data, here’s a video where Juan shows you step-by-step how to blend data on Looker Studio.
Alternatively, here’s an article we wrote on how to blend data using Looker Studio to show you how to get that done as soon as possible.
In this case, we blended our Facebook Ads Data with Google Merchandise Store using the left join option, and our preferred key is the Date column.
Now let’s say from the two data sources blended, which are Facebook ads and Google Merchandise Store, we want to – for whatever reason – See the total amount spent on Facebook ads and the purchases on the store platform each day. So here goes.
First, we will need to sum the Amount Spent Column and the Purchases field to see that number on our new column.
How do you get that done, you asked? No worries, here’s how you can combine both columns using a calculated field and regular expressions. Also, here’s an article on how to access and use calculated fields.
Click on the blended chart or table chart type. Now to your right – under Metric, click on “Add Metric” and click on “Add Field” like the image below.
Get 15 Days Porter Metrics Free Trial (worth $15)
Once that field opens up, now you can sum the column using this formula
Column 1 + Column 2
Once done with the combinations like the one shown below, click on Apply
Now you have the new metrics field “Sum Total” Added, and sure as anything, it will display null for the fields it wasn’t able to find figures.
Now that we have the Null values, we now need those null values to change to numeric zero, so we can either use that to get a sum total of the amount spent and the purchase completed per date or for any other arithmetic expression or mathematical operation.
This is where the NARY_MAX formula comes into play and will be helpful for what we want to do.
For clarity, I created a new column called Fixed Sum Total, where I pasted the NARY_MAX formula to demonstrate how the formula works so here goes –
In this context, I needed the null value to be zero on rows where the values were null for each scenario when I was trying to add the Amount Spent Column values with the Purchase Values.
I used the formula below for this purpose.
SUM(Amount spent) + SUM(NARY_MAX(Purchase Completed (Site Search Goal 1 Conversion Rate), 0))
For clarity purposes, I created a new field and then added the NARY_MAX formula from above to suit my unique case, which was to have a sum total of each row from the Amount Spent column and the Purchase column.
Now instead of having Null values as was the case in the Sum Total Column, with the NARY_MAX formula, we now have a true numeric null value which was used to sum Amount Spent and Purchase columns as shown below.
