Matchlists/tables in power query

问题:

I'm thinking this has to have a simple answer, but I can't find any examples.

I have to compare every member of a list to a list of substrings to see if that member contains a substring, and if it does - return the substring to a third list at the same position as the member of the first list.

Example:

ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"}
ListB = {"room", "me", "only"}

ListC should then should =  {"me", "room", null, "only"}

I'm an advanced programmer who has been writing M for about 4 days now. It's driving me crazy. I have been trying several different functions, but so far i'm not even close, so I'm not going to list my code. List.Transform seems the most likely option, but I can't quite work it out.

Thanks for the help,

-J

回答1:

Words intersections

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"room", "me", "only"},
    intersect=List.Transform(ListA, (lineA)=>Text.Combine(List.Intersect({Text.Split(lineA, " "), ListB}), "|"))
in
    intersect

Flags only

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