lookup value in another column

[llamasarefun]

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

[AlienSx · Accepted solution]

@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

[AlienSx]

@llamasarefun okay. CR LOOKUP VALUE is your column with lookup values. What is your lookup column? 

[llamasarefun]

Hi @AlienSx were you still able to help me with this please?

[AlienSx]

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

[llamasarefun]

Hi @AlienSx 

I have put this in and am getting the error 'A cyclic reference was encountered during evaluation'

[AlienSx]

@llamasarefun check this video out

[llamasarefun]

@AlienSx  sorry, im still not clear on this.  I am using the 'Custom Column' button then entering the code.  I am changing the 'your_table' to MASTER which is the name of my query as follows:

= Table.AddColumn(#"Renamed Columns2", "Custom", each let
Source = MASTER,
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)

[AlienSx]

@llamasarefun don't touch "Custom column" button, it's a big mistake. Have you seen that video? Your case is #2. Anyway, create new blank query, replace everything in it with my code and replace "your_table" in my code with the name of your query (MASTER?)   

[llamasarefun]

Hi @AlienSx  Yes I have watched the video thanks, very useful.  I am putting the code into blank query and get the error 

Expression.Error: The name 'Renamed Columns2' wasn't recognized. Make sure it's spelled correctly.

[AlienSx]

@llamasarefun my code does not have such "name". 

[llamasarefun]

@AlienSx yes sorry my mistake!

So no errors now but I cannot see where the new column where i would see the results is?

[AlienSx]

@llamasarefun rightmost column called "YN", located after column CR LOOKUP VALUE

[llamasarefun]

@AlienSx  there is no additional column, CR LOOKUP VALUE is the last one still

[AlienSx]

@llamasarefun 

[llamasarefun]

@AlienSx 

[AlienSx · Accepted solution]

@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 · Accepted solution]

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

[llamasarefun]

Hi, the lookup column is AMOUNT IN LOCAL CURRENCY

[llamasarefun]

AMOUNT IN LOCAL CURRENCY is the lookup column

[AlienSx]

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

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

[llamasarefun]

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]