Convert table to list and use that in function

[rbabu]

Hello friends,

I have managed to convert my table to a list which now looks like this for e.g "Afghanistan","Albania","Algeria","Andorra","Angola"

Name of the list - CountryLOV

Now I need to use this list in the function - List.Contains({CountryLOV},[ColumnName]). This is my syntax and its always returning false even if my column has the value belonging to that list. Pls tell me what am I doing wrong here. 

I have tried this as well List.Contains("{" & CountryLOV2 & "}",[ColumnName]). Reason being when I try to add the list as a column I dont see brackets on them, just the values. However, this is also failing.

Thanks a lot for reading.

[V-yubandi-msft · Accepted solution]

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.

[rbabu]

Hello, sorry for the delay. I have trying to do what you suggested without luck. I am unable to use Text.Split in sheet1 to make LOV(Value1) a list as it doesnt show up in the drop down for me to select 'Value1'. 

I am not able to attach my pbix to this reply. If you dont mind, could you try on your side pls. two tables. 1 table has the country column and other table has LOV (in the form of tables)

Thanks very much!

[V-yubandi-msft]

Hi @rbabu ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[rbabu]

Hello,

Thanks for following up. Am not very sure what to do from here, let me explain how my 2 table looks now.

So LOV has the values in a columns which could be countries for now. Assume they are A&B.

and Sheet1 has the column 'Customer Country' which I need to validate. I am unable to use Text.Split in sheet1 to make LOV(Value1) a list as it doesnt show up in the drop down for me to select 'Value1'

I want to do the below (I assume this is what you asked me to try, convert the LOV to a list first), but the highlighted values are not showing as a drop down when I type them.

Do correct me if my understading is right what you guided me to.

Thanks.

[V-yubandi-msft]

Hi @rbabu ,

Thank you for the detailed explanation and screenshots. You're on the right track. Just convert the LOV column into a proper list using Drill Down in Power Query. Then, add a Custom Column with this formula.

This will return TRUE if the value exists in the list, and FALSE if it doesn’t. No need for Text.Split() since you're working with a list, Power Query handles it automatically.
FYI:

I hope this helps..

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[rbabu]

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. 

[V-yubandi-msft]

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.

[V-yubandi-msft]

Hi @rbabu ,

Thanks for the update. We cannot convert the value '"Afghanistan","Albania",...' to type List.

It seems that CountryLOV2 is currently a text string, not a real Power Query list. Even though it looks like a list, Power Query still treats it as a single text value.

To fix this, you can convert the string into a real list using the Text.Split function. For example.

Then, you can  use:  List.Contains(CountryLOV2,[Country])

Helpful Docs:

Text.Split - PowerQuery M | Microsoft Learn
List.Contains - PowerQuery M | Microsoft Learn

If my response solved your query, please mark it as the Accepted solution to help others find it easily.

And if my answer was helpful, I'd really appreciate a 'Kudos'.

[rbabu]

Great. Yes your right. Its a string. Let me give that a try and get back. Thanks very much!

[V-yubandi-msft]

Feel free to update us once you try it out. 

Regards,

Yugandhar.

[rbabu]

I have tried this too - List.Contains(Text.ToList(CountryLOV),[Country]) , doesnt work.

[rbabu]

Yes thats what I tried as you suggested. My code now - List.Contains(CountryLOV2,[Country])

[rbabu]

Thanks for replying. I have tried that. I get this error - Expression.Error: We cannot convert the value ""Afghanistan","Alban..." to type List.

[Vijay_A_Verma]

Remove parentheses around CountryLOV, this is making entire list a single entry list. Hence, your formula would become List.Contains(CountryLOV,[ColumnName])