Replace yes found in one list with the actual string from another list

Question

I’m comparing two lists, ListA and ListB, to check if ListB’s row content appears in ListA’s row content at all. I can’t do a one-for-one match of both rows’ contents because the content of a row in ListB might only be one part of the content of a row in ListA.

PS.: In the query below, ListB includes “roo”, which is the first three letters in the word room. I would want to know that “roo” is in ListA’s row that has “in my room.”

What I want is to replace “yes” with the actual value from ListB — the value “roo,” for instance. I tried to simply substitute wordB for “yes” but wordB wasn’t recognized and an error was thrown at me.

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>if List.MatchesAny(ListB, (wordB)=>Text.Contains(lineA, wordB)) = true then "yes" else "no")
in
    contains_word

The current query results in the following :

    List
1   yes
2   yes
3   no
4   yes

I want the query results to be as the following :

    List
1   roo
2   me
3   
4   only

Any idea of how to do this?

Answer 1

Objective : The idea is to use List.Transform twice, so that; 

• Inner Transform : this list B is changed to leave only matching values
• Outer Transform : the string from list A is replaced by the first non-null entity.

Code :

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "me", "only"},
    contains_word=List.Transform(ListA, (lineA)=>List.Select(List.Transform(ListB, (wordB)=>if Text.Contains(lineA, wordB) = true then wordB else null), (x)=>x <> null){0}?)
in
    contains_word