Filter rows query with more than 1 column condition

[amay15]

I want to filter data as per below table

Test	Cost 1	Cost 2		Test	Cost 1	Cost 2
A	0	10		A	0	10
B	0	0		C	10	0
C	10	0		E	20	20
D	0	0
E	20	20

                Input                                                                                Output

IF Cost1= 0 & Cost2 = 0 then dont populate the row.

Any help will be appreciated

[Stachu]

I'm not sure I get the request - you need indication how to filter it in DAX for further calculations, or filter it in PowerQUery to reduce number of rows?

The DAX for this should be folllowing

Table Filtered = 
FILTER('Table','Table'[Cost 1]<>0 || 'Table'[Cost 2] <> 0) 

[amay15]

I want to apply this at query level then load the data. I dont want to create Column after data load.

Thank for quick reply. Looking forward for solution.

[Zubair_Muhammad · Accepted solution]

@amay15

Same logic we can apply in Query Editor as well

File attached

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIAYkMDpVidaCUnKBfCc4ZIwLguKJKuQJaRAYSIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Test = _t, #"Cost 1" = _t, #"Cost 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"Cost 1", Int64.Type}, {"Cost 2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.AllTrue({[Cost 1]=0, [Cost 2]=0})),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Bascially add a custom column to check condition

=List.AllTrue({[Cost 1]=0, [Cost 2]=0})

Then filter the records

[biswasg]

Thanks for your solution to OPs question. If however, one would want to add a number of conditions across the two columns, eg, in addition to 0 0, lets say also remove 20 20, what would be the added steps in the query?

[amay15]

Hi Zubair,

Thank you for your quick support. Appreciated!!

Question - Data load is very slow. I have huge data (approx. 10 lakh rows). Any suggestion to optimize my Query?

Regards,

Amay Singh

[Zubair_Muhammad]

@amay15

Also you can create a column and then use it as a VISUAL Filter

Column =
AND ( [Cost 1] = 0, [Cost 2] = 0 )