“How to replace zero values with null in BigQuery for weather data analysis?”
Missing values were incorrectly entered as zeroes, and I need to change them to null values. The code I used was Update (table0 Set (column)=0 WHERE (column) IS NULL. It keeps returning as an error.
I guess this data keeps getting updated all the time, right?
I suggest you make a view on top of it making the transformation. This way you preserve the original data and it opens the door for other cleaning tasks you might find in the future. And every time you have new data you don't need to update anything, all your queries will work.
The query for the view can be very simple. You will select all columns and you'll need is a case statement for replacing the zeroes. Something in the lines of
Select case when col = 0 then null else col end as new_col
Assuming also the column is a number, else you need to add quotes there.
NULLIF: Returns NULL if expr = expr_to_match evaluates to TRUE, otherwise returns expr.
since 0 is equal to 0, result would be null. You can put your column name to test it
SELECT NULLIF(0, 0) as result --> returns 0
SELECT NULLIF(10, 0) as result ---> returns 10 as 10 is not equal to 0
You can put your column name:
SELECT NULLIF(column,0) as result
