How to filter out certain rows in Power BI & Query?

by

User Question: “I have a large HR dataset with employee information, but some rows contain irrelevant or outdated data. How can I use Power Query in Power BI to filter out these unwanted rows efficiently?”

We often spend significant time out of our data analysis project doing data cleaning and preparation in Power BI. This tutorial will guide you through the process using an HR dataset as an example.

Assumed Data Model: Table name: EmployeeData, that has the following columns: EmployeeID (Text), Name (Text), Department (Text), HireDate (Date), Status (Text), Salary (Currency)

Remove specific rows in Power Query

  1. Open Power BI Desktop and load your EmployeeData table.
  2. Go to Home > Transform data to open Power Query Editor.
  3. In the Query Editor, select the column you want to filter (e.g., Status).
  4. Click on the filter icon in the column header.
  1. Choose the appropriate filter type:
  • For text columns: Select/deselect values or use text filters.
  • For date columns: Use date filters.
  • For numeric columns: Use number filters.
  1. For our example, let us go ahead and filter out inactive employees:
  • Click the filter icon on the Status column.
  • Uncheck “Inactive” to remove these rows.
  1. Replace the original query with this new filtered query.
  2. Click “Close & Apply” to apply changes and return to the Power BI report view.

Apply filters on multiple columns in Power BI

You can apply multiple filters either manually as shown above, but also filter out your data using M in the following way:

  • Add a custom column using the following M code:
let
    Source = EmployeeData,
    FilteredRows = Table.SelectRows(Source, each 
        [Status] <> "Inactive" and 
        [HireDate] > #date(2020, 1, 1) and 
        [Salary] > 30000
    )
in
    FilteredRows

Troubleshooting

Common issues and solutions you might encounter:

  • If you get unexpected results – Double-check your filter conditions and data types.
  • Performance issues with large datasets: Consider using index columns for faster filtering.
  • Missing data: Ensure your filter doesn’t inadvertently exclude important rows. Use “does not equal” instead of “equals” for text filters when appropriate.
  • Date format issues: Standardize date formats before applying date filters.