Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 okay. CR LOOKUP VALUE is your column with lookup values. What is your lookup column?
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'
@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?)
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 yes sorry my mistake!
So no errors now but I cannot see where the new column where i would see the results is?
@llamasarefun rightmost column called "YN", located after column CR LOOKUP VALUE
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
Hi, the lookup column is AMOUNT IN LOCAL CURRENCY
AMOUNT IN LOCAL CURRENCY is the lookup column
hi, @llamasarefun use List.Contains(lookup_value, ref_to_another_column)
or List.PositionOf(lookup_value, ref_to_another_column, Occurence.First) <> -1
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]