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 questions, I am doing a favour for my friend and it seems this task is too much for a beginner like me.

 

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"

8 Replies

  • 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"