Filtering with Different Criteria in Power Query

In this post, we will discuss Filtering in Power Query, focusing on:

• 1 Criterion

• Multiple Criteria within a Single Column

• Criteria Across Multiple Columns

The source referred to as 'data' contains columns for Item, Region, Size, and Value. We also have a criteria table called 'Criteria'.

Criteria-1 -> North in Region Column:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.SelectRows(Source("data") , (f) => f[Region] = Source("Criteria")[Region]{0})

in

 Result

Criteria-2 -> North OR South in Region Column:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.SelectRows(Source("data") , (f) => List.Contains(Source("Criteria")[Region], f[Region]))

in

 Result

Criteria-3 -> North Region AND Small Size OR South Region AND Medium Size:

let

 Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

 Result = Table.SelectRows(Source("data") , (f) => Table.Contains(Source("Criteria"), f[[Region],[Size]]))

in

 Result

f refers to each row in source data.