lookup value in another column

[Syndicate_Admin]

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

[Syndicate_Admin]

Hi, the lookup column is AMOUNT IN LOCAL CURRENCY

[Syndicate_Admin]

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

[Syndicate_Admin]

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

[Syndicate_Admin]

Hi @AlienSx 

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

[Syndicate_Admin]

@llamasarefun check this video out

[Syndicate_Admin]

@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)

[Syndicate_Admin]

@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?)   

[Syndicate_Admin]

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.

[Syndicate_Admin]

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

[Syndicate_Admin]

@AlienSx yes sorry my mistake!

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