cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.