Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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)
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.
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
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?
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
Also you can create a column and then use it as a VISUAL Filter
Column = AND ( [Cost 1] = 0, [Cost 2] = 0 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |