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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors