How can I filter blank rows from my dataset in Power BI?

by

When preparing our data for analysis we often deal blank rows which oftern skew our analysis and visuals. Let’s explore how to efficiently filter out these empty entries using both Power BI and Power Query, ensuring your data is clean and ready for analysis.

Assumed data model: HR_Data (EmployeeID: Text, Name: Text, Department: Text, Salary: Currency, HireDate: Date)

Removing null rows in Power Query

As always, we prefer to manipulate the data as close as possible to its source. When we don’t have access to the actual data source being a SQL database, No SQL databases, Excel or other files, we’ll try to use cleanse the data in Power BI:

  1. Go to “Home” > “Edit Queries” to open the Power Query Editor.
  2. Select your “HR_Data” query.
  1. Go to “Home” > “Remove Rows” > “Remove Blank Rows”.
  2. If you need to remove rows with blanks in specific columns:
    a. Select the relevant columns.
    b. Right-click > “Remove Empty”.
  1. For a more customized approach, add a custom column:
= if [Name] <> null and [Department] <> null and [Salary] <> null and [HireDate] <> null then true else false
  1. Filter this column to keep only “true” values.
  2. Remove the custom column if no longer needed.

Filter empty rows in Power BI

  1. Open your report in Power BI Desktop.
  2. Select the table visual containing your data.
  3. In the Filters pane, locate the column you want to filter (e.g., “Name”).
  4. Click the dropdown arrow and select “Filter type” > “Advanced filtering”.
  5. In the advanced filter dialog, set the condition: “is not blank”.
  6. Click “Apply filter”.

For a more robust solution using DAX:

Filtered_HR_Data = 
FILTER(HR_Data, 
    NOT(ISBLANK(HR_Data[Name])) &&
    NOT(ISBLANK(HR_Data[Department])) &&
    NOT(ISBLANK(HR_Data[Salary])) &&
    NOT(ISBLANK(HR_Data[HireDate]))
)

This measure creates a filtered version of your table, excluding rows where any of the specified columns are blank.

What to do if something goes wrong?

  • If rows aren’t being removed, check for whitespace characters – Use the TRIM() function in DAX or the Trim transformation in Power Query to remove leading/trailing spaces.
  • Ensure date columns are properly formatted. Blank dates might be interpreted as actual values.
  • For numerical columns, consider using ISBLANK() in combination with ISERROR() to catch both blank and error values.
  • If filtering in Power Query affects relationships, update them in the model view after applying changes.