Return the match from List.ContainsAny

[Anonymous]

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 works perfectly, returning 1 for a match and 0 for no match. But how can I return the value that was matched?

for example:

I have a row, where ProductsList contains the following "ABA|ABB|ABC|ABD|ABE|ABF"

And my ExceptionList contains {ABD,ABJ,ABZ}

I would like to return the value ABD instead of 1.

It's unlikely there will be more than 1 match, but if it's possible to just return the first?

IS this even possible?

[Anonymous · Accepted solution]

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"

[v-alq-msft]

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.

[cerebro]

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!

[Anonymous]

give a try to this

let
l1=Text.Split("ABA|ABB|ABC|ABD|ABE|ABF","|"),
l2={"ABD","ABJ","ABF"}
in List.Transform(List.PositionOfAny(l1,l2,Occurrence.All), each l1{_})

if you need only one element change occurrence.all with occurrence.first, for example

[Anonymous]

@Anonymous  Hi. Thanks for this, I think this does what I want. BUT, I'm trying to convert your line

in List.Transform(List.PositionOfAny(l1,l2,Occurrence.All), each l1{_})

into an added collumn, by passing in my data as follows:

Let
  Source = My Connection to the data
  l1 = Text.Split([ProductList],"|"),
  l2 = #"Exceptions"[ExceptionProducts],
  #"Added Custom1" = Table.AddColumn(#"Changed Type", "Exception", each List.Transform(List.PositionOfAny(l1,l2,Occurrence.First), each l1{_}))
in
  #"Added Custom1"  

 What am I doing wrong?

[Anonymous · Accepted solution]

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"

[Anonymous · Accepted solution]

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"

[Anonymous]

@Anonymous Great solutions. Thanks for your help