Forum Discussion

llamasarefun's avatar
llamasarefun
Helper I
2 years ago
Solved

lookup value in another column

hi, I am trying to lookup a value from one column to see if it exists in another column and then return either a yes or no.

 

Can this be done easily in power query?

 

thanks

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    llamasarefun 

    1. Create blank query

    2. Open Advanced Editor

    3. Replace everything you see there with my code

    4. Replace your_table with MASTER in the code

     

25 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    hi, llamasarefun use List.Contains(lookup_value, ref_to_another_column)

    or List.PositionOf(lookup_value, ref_to_another_column, Occurence.First) <> -1

    • llamasarefun's avatar
      llamasarefun
      Helper I
      2 years ago

      Hi, thanks for the quick reply.  I have tried using the List.contains formula but I am getting an error saying

       

      Expression.Error: We cannot convert the value 312 to type List.
      Details:
      Value=312
      Type=[Type]

       

       

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        you need to refer to the column of the WHOLE table, not it's value in the currect row. Such reference looks like table_name[column_name]

      • AlienSx's avatar
        AlienSx
        Super User
        2 years ago

        Hi, llamasarefun . CR LOOKUP VALUE is basically equal to AMOUNT IN LOCAL CURRENCY * -1 in any given row of your table (sign aside). Lets suggest that you would like to check if your table has more than one row with such amount. Then try this

        let
    Source = your_table,
    lu_column = List.Buffer(Source[CR LOOKUP VALUE]),
    yes_no = 
        Table.AddColumn(
            Source, "YN", 
            each 
                if List.Count(List.PositionOf(lu_column, [CR LOOKUP VALUE], Occurrence.All)) > 1
                then "YES"
                else "NO"
        )
in
    yes_no