How do you handle null, 0, and blank values in your data during the cleaning process?

Sometimes interview questions are also based on this topic. Many data aspirants or even some professionals sometimes make the mistake of simply deleting missing values or trying to fill them without proper analysis.This can damage the integrity of the analysis. It’s essential to ask or find out the reason behind missing values in the data
whether from the project head, client, or through own investigation.

𝘼𝙣𝙨𝙬𝙚𝙧:

Handling null, 0, and blank values is crucial for ensuring the accuracy and reliability of data analysis. Here’s how to approach it:

1. 𝙄𝙙𝙚𝙣𝙩𝙞𝙛𝙮𝙞𝙣𝙜 𝙖𝙣𝙙 𝙐𝙣𝙙𝙚𝙧𝙨𝙩𝙖𝙣𝙙𝙞𝙣𝙜 𝙩𝙝𝙚 𝘾𝙤𝙣𝙩𝙚𝙭𝙩:
   - 𝙉𝙪𝙡𝙡 𝙑𝙖𝙡𝙪𝙚𝙨: These represent missing or undefined data. Identify them using functions like 'ISNULL' or filters in Power Query.
   - 0 𝙑𝙖𝙡𝙪𝙚𝙨: These can be legitimate data points but may also indicate missing data in some contexts. Understanding the context is important.
   - 𝘽𝙡𝙖𝙣𝙠 𝙑𝙖𝙡𝙪𝙚𝙨: These can be spaces or empty strings. Identify them using 'LEN', 'TRIM', or filters.

2. 𝙃𝙖𝙣𝙙𝙡𝙞𝙣𝙜 𝙏𝙝𝙚𝙨𝙚 𝙑𝙖𝙡𝙪𝙚𝙨 𝙐𝙨𝙞𝙣𝙜 𝙋𝙧𝙤𝙥𝙚𝙧 𝙏𝙚𝙘𝙝𝙣𝙞𝙦𝙪𝙚𝙨:
   - 𝙉𝙪𝙡𝙡 𝙑𝙖𝙡𝙪𝙚𝙨: Typically decide whether to impute, remove, or leave them based on the dataset’s context and the analysis requirements. Common imputation methods include using mean, median, or a placeholder.
   - 0 𝙑𝙖𝙡𝙪𝙚𝙨: If 0s are valid data, leave them as is. If they indicate missing data, treat them similarly to null values.

   - 𝘽𝙡𝙖𝙣𝙠 𝙑𝙖𝙡𝙪𝙚𝙨: Convert blanks to nulls or handle them as needed. This involves using 'IF' statements or Power Query transformations.

3. 𝙐𝙨𝙞𝙣𝙜 𝙀𝙭𝙘𝙚𝙡 𝙖𝙣𝙙 𝙋𝙤𝙬𝙚𝙧 𝙌𝙪𝙚𝙧𝙮:
   - 𝙀𝙭𝙘𝙚𝙡: Use formulas like 'IFERROR', 'IF', and 'VLOOKUP' to handle these values.
   - 𝙋𝙤𝙬𝙚𝙧 𝙌𝙪𝙚𝙧𝙮: Use transformations to filter, replace, or fill null and blank values. Steps like 'Fill Down', 'Replace Values', and custom columns help automate the process.

By carefully considering the context and using appropriate methods, the data cleaning process maintains the integrity and quality of the data.

Hope it helps :)