Forum Discussion
Comparing values from two columns and write result in third one
In the query-editor (!) you can add a column with this formula:
List.Contains(NameOfThePreviousStep[ID1], [ID2])
This will check, if the value of the current row from column "ID2" matches any occurances within column "ID1". In order to search the whole column "ID1", you need to prefix it with the name of the previous step in your query.
This is a sample code, which demonstrates it if you paste it into the advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]), ChgType = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}}), #"Added Custom" = Table.AddColumn(ChgType, "Exists", each List.Contains(ChgType[ID1], [ID2])) in #"Added Custom"
In this scenario, you just need to create a calculated column like:
Column = IF(Table[ID1]=Table[ID2],BLANK(),"True")
Regards,
Thank you for your reply. I think we have a misunderstanding here. I am not looking for expression to compare values in the same row, this is pretty straight forward and I can do that.
I am looking for expression which would return "true" only for those ID2 values, which are not present in column 1 (ID2).
Best regards
Matt
In the query-editor (!) you can add a column with this formula:
List.Contains(NameOfThePreviousStep[ID1], [ID2])
This will check, if the value of the current row from column "ID2" matches any occurances within column "ID1". In order to search the whole column "ID1", you need to prefix it with the name of the previous step in your query.
This is a sample code, which demonstrates it if you paste it into the advanced editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYnWglIyDLWCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID1 = _t, ID2 = _t]), ChgType = Table.TransformColumnTypes(Source,{{"ID1", Int64.Type}, {"ID2", Int64.Type}}), #"Added Custom" = Table.AddColumn(ChgType, "Exists", each List.Contains(ChgType[ID1], [ID2])) in #"Added Custom"
