List.FindText with items from a list?

[ValeriaBreve]

Hello,

I have 2 lists:

ListData

ListFilter

both containing text strings.

I would like to find any item in ListData that contains any part of a string from ListFilter.

ex.

ListData: {"red apple", "blue apple", "green banana", "red pear"}

ListFilter:{"apple","pear"}

Desired result:  {"red apple", "blue apple", "red pear"}

I have tried List.FindText which works wonderfuly with an unique text - but how do you adapt it to scanning through a list?

Thanks a lot!

Kind regards

Valeria

[Muhammad_Ahmed]

Hi,
How I will handle this.
let
Source = {"red apple", "blue apple", "green banana", "red pear"},
Custom1 = {"apple","pear"},
Custom2 = List.Select(Source, each List.AnyTrue(List.Transform(Custom1, (substring) => Text.Contains(_, substring))))
in
Custom2

[Ahmedx]

pls try

let
       f=(x)=> List.Select(x, (y)=> List.Count( Splitter.SplitTextByAnyDelimiter( List.Transform(ListFilter,Text.Lower))(y))>1),
     Source = {"red apple", "blue apple", "green banana", "red pear"},
    Custom1 = f(Source)
in
  Custom1

[ronrsnfld · Accepted solution]

Another approach, using List.Accumulate to cycle through the ListFilter:

let
    ListData= {"red apple", "blue apple", "green banana", "red pear"},
    ListFilter={"apple","pear"},
    filter = List.Accumulate(
        ListFilter,
        {},
        (s,c)=> s & List.FindText(ListData,c))    
in
    filter

[ValeriaBreve]

@ronrsnfld  @ZhangKun both your solutions work but only partially for me (which I know it's impossible). Still In my filter list I have items like "Q1". In my data list I have an item "2024 Q1" and it won't be returned. I checked the spelling, no trailing spaces...do you have an idea what might be happening? Thanks!

[ronrsnfld]

There may be something wrong with how you implemented my solution.

Adding the item "2024 Q1" to ListData and "Q1" to ListFilter includes "2024 Q1" in the returned list.

let
    ListData= {"2024 Q1","red apple", "blue apple", "green banana", "red pear"},
    ListFilter={"apple","Q1"},
    filter = List.Accumulate(
        ListFilter,
        {},
        (s,c)=> s & List.FindText(ListData,c))    
in
    filter

Results (as expected):

[ValeriaBreve]

@ronrsnfld @ZhangKun Thanks! I don't know what was happening... I did a couple of transformations to the ListData - which change nothing to items like "2024 Q1" - and now it works. Never seen this before, and I don't understand it. Anyway - again thanks - with this it works perfeclty 🙂 

[ZhangKun]

My answer did not use the List.FindText function (it is easier to use List.FindText), so I deleted the answer. If you still have problems, you should provide the data row in question, such as "2024 Q1" as you said.

[ValeriaBreve]

Hi @ZhangKun thanks but what a pity! Your solution was working perfectly as well and was a nice alternative 🙂 Thanks!

[Ahmedx]

Can you share sample data (ListData and  ListFilter) ?

[ValeriaBreve]

@Ahmedx Hello,

ListData: {"red apple", "blue apple", "green banana", "red pear"}

ListFilter:{"apple","pear"}

Desired result:  {"red apple", "blue apple", "red pear"}