Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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"

     

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
      cerebro
      Helper I
      3 years ago

      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!