How to Filter Rows in Power Query in Power BI

To filter rows in Power Query in Power BI, use the Table.SelectRows function or the filter options in the Power Query Editor UI. This lets you keep only rows that meet specific conditions by specifying a logical test on columns.

Syntax

The main syntax to filter rows in Power Query M language is:

Table.SelectRows(table as table, condition as function) as table

Here:

• table is the input table you want to filter.
• condition is a function that returns true for rows to keep and false for rows to remove.

m

Table.SelectRows(Source, each [ColumnName] = "Value")

Example

This example filters a table to keep only rows where the Country column equals "USA".

m

let
    Source = Table.FromRecords({
        [Name="John", Country="USA", Age=30],
        [Name="Anna", Country="Canada", Age=25],
        [Name="Mike", Country="USA", Age=40]
    }),
    FilteredRows = Table.SelectRows(Source, each [Country] = "USA")
in
    FilteredRows

Output

[
  {"Name": "John", "Country": "USA", "Age": 30},
  {"Name": "Mike", "Country": "USA", "Age": 40}
]

Common Pitfalls

• Using incorrect column names causes errors; column names are case sensitive.
• For text comparisons, use quotes around values, e.g., "USA".
• Logical conditions must return true or false for each row.
• Using UI filters and then editing M code separately can cause conflicts.

m

/* Wrong: Missing quotes around text value */
Table.SelectRows(Source, each [Country] = USA)

/* Right: Text value in quotes */
Table.SelectRows(Source, each [Country] = "USA")

Quick Reference

Action	Syntax Example	Description
Filter rows by equality	Table.SelectRows(Source, each [Column] = "Value")	Keeps rows where column equals value
Filter rows by number condition	Table.SelectRows(Source, each [Age] > 30)	Keeps rows where Age is greater than 30
Filter rows by multiple conditions	Table.SelectRows(Source, each [Country] = "USA" and [Age] > 25)	Keeps rows matching both conditions
Filter rows using UI	Use filter dropdown in Power Query Editor	Click column filter icon and select values or conditions

Key Takeaways

Use Table.SelectRows with a condition function to filter rows in Power Query.

Column names and text values must be exact and case sensitive in conditions.

You can filter rows both by UI filters or by writing M code for more control.

Logical conditions must return true or false for each row to keep or remove it.