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

     

  • llamasarefun's avatar
    llamasarefun
    2 years ago

    AlienSx Brilliant, that has worked perfectly, thanks so much for your help!

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]

      • llamasarefun's avatar
        llamasarefun
        Helper I
        2 years ago

        thanks,

         

        I thought I was taking the whole column, my formula looks like this: