Filter by comparing 2 columns

[Anonymous]

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

[Anonymous · Accepted solution]

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

[lbendlin]

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