Forum Discussion
Convert table to list and use that in function
Hi rbabu ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi rbabu ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hello,
Sorry for the delay, got busy with other tasks. I have tried now. But not sure if I am doing it correctly.
Right now my LOV is a table which I have converted to a list and the values are - screen shot below.
Then I added a new columns and added this - List.Contains(Text.Split(LOV,","),[Country])
It still keeps returning FALSE.
Are you able to advise if I am doing the right thing? I am unable to see it in the format which you gave as an example.
Hi rbabu ,
Thank you for the update. Your formula List.Contains(Text.Split(LOV,","),[Country]) returns FALSE due to how Power Query handles references across tables.
1. The LOV column is in the A_C_LOV table, but Sheet1 doesn’t have its own LOV column. So, when you reference LOV directly in Text.Split(LOV,","), Power Query cannot determine which value to use, causing the formula to fail.
2. Even if it worked, Text.Split() creates a list with quotes, which won’t match [Country] values like Afghanistan (without quotes).
So : 1. Convert the LOV string from A_C LOV into a proper list in a separate query.
2. Use that list in Sheet1 to check whether [Country] values exist in it.
Note: Don’t wrap LOV in {} doing that would create a list within a list, which caused the error earlier.
Regards,
Yugandhar.