Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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?

 

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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"

 

image.png

 

 

image.png

 

 

 

 

 

View solution in original post

Anonymous
Not applicable

another way, may be cleaner, could be this which use a user defined function:

 

image.png

 

 

 

let
  Source = tab,

  l2={"ABD","ABJ","ABF"},
  #"Added Custom1" = Table.AddColumn(Source, "Exception", each extract_exception([ProductList],l2))
in
  #"Added Custom1"

 

 

image.png

View solution in original post

7 REPLIES 7
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

c1.png

 

You may create a custom column and input the following codes.

c4.png

let x =
List.Intersect(
    {
        Text.Split([ProductsList],"|"),
        #"Changed Type"[Exceptionlist]
    }
)
in 
if 
List.Count(x)>0
then x{0} else ""

 

Result:

c5.png

 

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!

Anonymous
Not applicable

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
Not applicable

@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
Not applicable

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"

 

image.png

 

 

image.png

 

 

 

 

 

Anonymous
Not applicable

another way, may be cleaner, could be this which use a user defined function:

 

image.png

 

 

 

let
  Source = tab,

  l2={"ABD","ABJ","ABF"},
  #"Added Custom1" = Table.AddColumn(Source, "Exception", each extract_exception([ProductList],l2))
in
  #"Added Custom1"

 

 

image.png

Anonymous
Not applicable

@Anonymous Great solutions. Thanks for your help

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.