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"))
)