Forum Discussion
Return the match from List.ContainsAny
I'm using the following to check a list of products in a string against a list from a table: if List.ContainsAny(Text.Split([ProductsList],"|"),#"ExceptionProducts"[Product]) then 1 else 0 This ...
try this
let Source = tab, l2={"ABD","ABJ","ABF"}, #"Added Custom1" = Table.AddColumn(Source, "Exception", each let l1=Text.Split([ProductList],"|"),p=List.PositionOfAny(l1,l2,Occurrence.First) in try l1{p} otherwise null) in #"Added Custom1"another way, may be cleaner, could be this which use a user defined function:
let Source = tab, l2={"ABD","ABJ","ABF"}, #"Added Custom1" = Table.AddColumn(Source, "Exception", each extract_exception([ProductList],l2)) in #"Added Custom1"
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a custom column and input the following codes.
let x =
List.Intersect(
{
Text.Split([ProductsList],"|"),
#"Changed Type"[Exceptionlist]
}
)
in
if
List.Count(x)>0
then x{0} else ""
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello
do you know how to adjust your formula if the exeptionlist is in the other table/source?
AnotherSouce[Exceptionlist] does not work here
thank you in advance!
