How to remove rows based on value in Power Query?

by

Here’s a question from our Facebook group:

I have a Power BI dataset with employee information, but I need to remove rows where employees have left the company. How can I use Power Query to filter out these rows based on a ‘Status’ column?

Filter Power Bi rows based on column values

Filtering out irrelevant data is crucial for accurate analysis. In this tutorial, we’ll explore how to remove rows based on specific values using Power Query in Power BI, focusing on an HR dataset. We will demo this capability by excluding former employees from our data.

Understand your dataset

We will use our well known fictitious EmployeeData table.

Table Name:

  • EmployeeData

Column Names:

  • EmployeeID (Number)
  • FirstName (Text)
  • LastName (Text)
  • Department (Text)
  • Status (Text)
  • HireDate (Date)
  • TerminationDate (Date)

Removing rows by condition in Power BI

Power Query allows for data transformation processes before loading into the Power BI model. This reduces the dataset size and improves load and query performance.

  • Open your Power BI Desktop app and load your dataset.
  • Go to “Home” tab and click “Transform Data” so that you can launch the Power Query Editor.
  • Select the your specific table in the Queries pane. In our case, that will be the EmployeeData table.
  • Click on the drop-down arrow next to the Status column header.
  • Uncheck the box next to Terminate” to filter out these rows.
  • Click OK to apply the filter.

Filter columns with Power Query M language

This code loads the Excel file, selects the “EmployeeData” table, and filters out rows where the Status is “Terminated”. You can run this from the Power Query Advanced Editor.

let
Source = Excel.Workbook(File.Contents("C:\path\to\your\file.xlsx"), null, true),
EmployeeData_Table = Source{[Item="EmployeeData",Kind="Table"]}[Data],
FilteredRows = Table.SelectRows(EmployeeData_Table, each [Status] <> "Terminated")
in
FilteredRows


Troubleshooting

If rows aren’t being removed as expected:

  • Check for inconsistent spelling or capitalization in your column not allowing your to filter by condition.
  • Ensure there are no leading or trailing spaces in the values.
  • If using M code, verify the column name matches exactly.
  • If the filter isn’t applying, try refreshing the query or closing and reopening Power BI Desktop.