How to check if cells contain null values in Power Query?

by

I’m working with employee data in Power BI and need to identify missing values in certain columns. How can I use Power Query to check for null values and handle them appropriately?


Checking for Null Values in Power Query

This tutorial will guide you through the process of identifying and handling null values in Power Query using an HR dataset as an example.
We will work with an Employee dataset containing empty values which we would like to cleanup for further analysis in Power BI.

Step-by-Step Instructions

  • Open Power BI Desktop and load your EmployeeData table.
  • Go to Home > Transform data to open Power Query Editor.
  • To check for null values in a specific column:
  • a. Select the column you want to check (e.g., Department).
  • b. Go to Home > Filter > Filter Rows > Is Null.
  • To replace null values with a specific value:
    • Select the column (e.g., Department).
    • Go to Transform > Replace Values.
    • In the “Replace Values” dialog input the following values: Value To Find: null Replace With: “Unassigned”
    • Click OK.

Create column to mark rows containing nulls

Power Query provides various methods to identify and handle null values.
Custom columns can be created to flag records with null values across multiple columns.

  • To create a custom column indicating null values:
  • Go to Add Column and thenpick Custom Column.
  • Name the column “HasNullValues”.
  • Enter the following formula (using the M language)
= Table.AddColumn(#"Previous Step", "HasNullValues", each if [Department] = null or [Salary] = null or [HireDate] = null then true else false)

Filter rows with any null values in Power BI

  • Select the “HasNullValues” column.
  • Go to Home > Filter > Filter Rows > Equals > True.
  • Last, go ahead and hit the Close and Apply button to save the changes in Power Query Editor.

If you encounter issues:

  • Ensure your column names match exactly in formulas.
  • Check data types: null checks may not work on columns with mixed data types.
  • If replacing nulls doesn’t work, try using the “Replace Errors” option in Power Query instead.
  • For date columns, consider using Date.FromText() function to handle potential formatting issues.