Trouble Converting Zeroes to Null Values in BigQuery

Question

I'm currently working as a junior data analyst at a local news station, tasked with analyzing weather trends using BigQuery. In my analysis, I encountered a problem with the data cleaning process. The newest dataset hasn't been properly cleaned, resulting in missing values being incorrectly entered as zeroes. Before proceeding with my analysis, I need to convert these zeroes to null values. However, the code I'm using to do this is returning an error message, and I'm unsure how to proceed.

What I've Tried: I've attempted to use the IF and CASE functions in BigQuery to convert the zeroes to null values, but I keep receiving error messages. Ideally, I expected the code to run without errors and for the zeroes to be successfully converted to null values. However, I'm encountering difficulties in achieving this outcome. Minimal Reproducible Example:

SELECT IF(wind_speed = 0, NULL, wind_speed) AS cleaned_wind_speed FROM weather_data WHERE year = 2023;

Answer 1

You can try to use COALESCE function in bq. It will return wind_speed if available, otherwise it returns NULL