Double condition for a column filtering in powerquery

[LoryMenCR]

Dear All,

i'm struggling to define a double condition for a column filtering in powerquery.

I need to filter out from the table when there is a specific value in one column AND another specific value in another column.

Let's say I have the following table:

Fruit	Number	Color
Apple	2	Blue
Banana	3	Yellow
Grapes	0	Orange
Watermelon	0	Black

I need to filter out rows where there is "0" in Number column and "Orange" in Color column, so I get the following table:

Fruit	Number	Color
Apple	2	Blue
Banana	3	Yellow
Watermelon	0	Black

Do you have any idea?
Thanks a lot

[Anonymous]

Hi @LoryMenCR 
you can create a custom column in power query with condition 

RemoveRow = if [Value] = 0 and [Color] = "Orange" then "Remove" else "Keep"

then you can filter out the "Remove" from the RemoveRow column. This will keep only the rows where the condition Value= 0 and Color= "Orange" is not satisfied.

[rajendraongole1 · Accepted solution]

Hi @LoryMenCR - In Power query editor,based on two conditions, you can use the Filter Rows feature with a custom filter or use M code directly

This should effectively remove the row with Number = 0 and Color = "Orange".

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElV0lEyAmKnnNJUpVidaCWnxDwgBIoYA3Fkak5OfjlY3L0osSC1GChmAMT+RYl56RD14YklqUW5qTn5eVA5p5zE5Gyl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruit = _t, Number = _t, Color = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Fruit", type text}, {"Number", Int64.Type}, {"Color", type text}}),
#"FilteredRows" = Table.SelectRows(Source, each not ([Number] = "0" and [Color] = "Orange"))
in
#"FilteredRows"

Hope it works