Filter by comparing 2 columns

Anonymous — Wed, 02 Mar 2022 14:06:31 GMT

I have a table with multiple columns. I want to filter out a record if column A = column C. I've seen many variations but not this one in the developer forum. Any suggestions?

Thanks!

Jean

Re: Filter by comparing 2 columns

lbendlin — Thu, 03 Mar 2022 20:05:44 GMT

currently only possible as a calculated column or measure flag. If this is important to you please consider raising it at https://ideas.powerbi.com

Re: Filter by comparing 2 columns

Anonymous — Mon, 07 Mar 2022 05:55:06 GMT

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Suppose you have a table as follows

A	B	C
aa	11	ee
bb	22	bb
cc	33	aa
dd	44	ff
gg	55	vv

2. Add a custom column to judge whether the value of A column is equal to the vlaue of C column, if yes then 1 else 0

3. Filter the records which the value of custom column is 0

4. Remove the custom column

The full applied codes as follows:

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hcq3DQAxEAPBXhgrkavmcIF8B6r/V58MFgTN1JqCYoS15MHUO50SUG8Yg84Z+L5hTroU2PsfzqFrhXvl/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", Int64.Type}, {"C", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "A=C?", each if [A] = [C] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"A=C?"] = 0)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"A=C?"}) in #"Removed Columns"

Best Regards

topic Re: Filter by comparing 2 columns in Developer

Filter by comparing 2 columns

Re: Filter by comparing 2 columns

Re: Filter by comparing 2 columns