Checking all items from a list are found in a comma separated text column

[Betty888]

Dear all,

I have an issue with a query that I’m using to check whether all items from two lists are found in two text columns with comma separated. Here are few explanation :

I have a column “Code” concatenating product codes comma separated, and a second column “Type” concatenating product types comma separated also . My objective is to check that 2 specific codes AND 2 specific types are found in these 2 columns as here below :

Code	Type	found
A,B	ProductA,ProductB, ProductD	Ok
A,B,C,D	ProductR,ProducG, ProductF	No
C,D,E,F	ProductA,ProductB, ProductD	No

For that, I’m using the query below :

= each if List.AllTrue(List.Transform({"A","B"}, (substring) => Text.Contains ([Code] , substring, Comparer.OrdinalIgnoreCase))) = true  and List.AllTrue(List.Transform({"ProductA","ProductB"}, (substring) => Text.Contains ([Type] , substring, Comparer.OrdinalIgnoreCase))) = true then  "Ok" else "No"

But it seems not working properly as it’s not considering the comma separator, I’m saying that because cases where Code = “AB” returns an OK which makes me thinking that the comma separator is not taken into account in my query.

Thanks a lot for your help !

[rajendraongole1 · Accepted solution]

Hi @Betty888 - Modified Power Query code to ensure the comma separator is properly considered

= each let
CodeList = Text.Split([Code], ","),
TypeList = Text.Split([Type], ","),
CodesFound = List.AllTrue(List.Transform({"A","B"}, (substring) => List.Contains(CodeList, substring))),
TypesFound = List.AllTrue(List.Transform({"ProductA","ProductB"}, (substring) => List.Contains(TypeList, substring)))
in
if CodesFound and TypesFound then "Ok" else "No"

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!