Search code examples
exceldataframepowerquerym

Nested Filtering in PowerQuery

Using PowerQuery and given the following, filter the dataset to include children not age 9 or age 10 and children age 7 who are in class A or class B

INPUT

Name Age Class
Jerry 9 A
Jim 7 C
Jones 8 C
Jamie 8 A
Jaquin 7 C
Jim-Bo 7 A
Calvin 10 A
Hermit 7 B
Ahsoka 11 B

OUTPUT

Name Age Class
Jones 8 C
Jamie 8 A
Jim-Bo 7 A
Hermit 7 B
Ahsoka 11 B

I have tried to figure it out on my own but the "nested" conditional messes it up I think.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each [Age] <> 9 and [Age] <> 10 or ([Age] = 7 and ([Class] = "A" or [Class] = "B")))
in
    #"Filtered Rows"
Solution

  • The trick is to include Age<>7 in the first part of the or, and to use () correctly

    #"Filtered Rows"= Table.SelectRows(Source, each 
    ([Age] <> 9 and [Age] <> 10 and [Age]<>7) or
     ([Age]=7 and ([Class]="A" or [Class]="B"))
)