Forum Discussion

skype_see4d's avatar
skype_see4d
Frequent Visitor
9 years ago
Solved

Comparing values from two columns and write result in third one

Hello, I have two columns. I want to compare values from second one and if there is a value which is not present in the first column, write value "true" into new column. I know these are basic quest...
Need Help
  • ImkeF's avatar
    ImkeF
    9 years ago

    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"
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

skype_see4d

 

In this scenario, you just need to create a calculated column like:

 

Column = IF(Table[ID1]=Table[ID2],BLANK(),"True")

Regards,

 

skype_see4d's avatar
skype_see4d
Frequent Visitor
9 years ago

v-sihou-msft

 

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

 

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    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"
    • smpa01's avatar
      smpa01
      Community Champion
      8 years ago

      ImkeFthis is a very nice lookup code.

       

      You are using this code to compare two columns within same table. Is it possible to use List.Contains to lookup ID1 column in table 1 to ID2 in table 2 by any chance.

       

       

      • ImkeF's avatar
        ImkeF
        Community Champion
        8 years ago

        It would work like this:

         

        List.Contains(NameOfTheQuery[ID1], [ID2])

        But it might be slow on large tables.

        Instead you can merge the 2 tables and instead of expanding the merged column, create a new one where you check if the merged column is empty or not: 

         

        Table.IsEmpty([MergedColumn])

        This will return a true/false-column.