Forum Discussion
lookup value in another column
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 Brilliant, that has worked perfectly, thanks so much for your help!
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
Hi AlienSx
I have put this in and am getting the error 'A cyclic reference was encountered during evaluation'
llamasarefun check this video out
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)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?)