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)Test Power Query nulls without confusing them with blanks or errors
A safe diagnosis changes one condition at a time and checks the result immediately. Use explicit null comparison and handle errors separately. Record the account, device or client, and exact symptom so every later test uses the same starting point. Avoid combining resets, reinstalls, and permission changes because a successful result would not identify which action mattered.
Keep the first test reversible
Validate counts before and after filtering. Use non-sensitive sample data and a standard user account whenever possible. If the sample succeeds, compare its location, ownership, policy, and data shape with the failing item rather than widening the repair to the whole application.
Preserve representative samples of removed records for review. Preserve the original file, configuration, or identifier until the result is confirmed. A locked option or organization message is evidence of an administrative boundary, not an invitation to bypass policy.
Verify normal operation after the change
undefined. Close and reopen the relevant app, then repeat the ordinary workflow. Check a second client or observer only when it answers a specific question about synchronization, permissions, hardware, or service availability.
When results differ, note the app version, account type, time, network, and exact error. That information separates a device problem from account, data, permission, or service behavior and gives support a focused case. Remove personal names, addresses, document text, and tokens from screenshots before sharing them.
Have another authorized user repeat the shortest safe test if shared access is involved. Their result may expose owner-only privileges, stale state, or an assumption about storage. Keep the known-good configuration available until the change survives a restart, refresh, or normal work cycle, and reverse experimental settings that did not help. Check the outcome again after the next scheduled synchronization when timing can affect the feature.
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.