Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (17.6k points)

I found part of what I was looking for at Matchlists/tables in power query, but I need a bit more.

Using the "Flags only" example provided at Matchlists/tables in power query, 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 (like with List.Intersect) because the content of a row in ListB might only be part of the content of a row in ListA.

Note that, 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.”

The "Flags only" example provided by Matchlists/tables in power query already determines that “roo” is part of ListA’s row that has “in my room.” I built on the example to assign “yes,” instead of true when there is such a match between the ListA and ListB.

What I’d like to do is to replace “yes” with the actual value from ListB — the value “roo,” for instance. I tried to simply substitute wordB for “yes” but I got an error that wordB wasn’t recognized.


    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")



The current query results in this:


1   yes

2   yes

3   no

4   yes 

I want the query results to be:


1   roo

2   me


4   only 

Any idea how to make it so?

(p.s. I'm extremely new to Power Query / M)



1 Answer

0 votes
by (47.2k points)
selected by
Best answer
  • We can use List.Transform twice: inner one changes list B to leave only matching values. Then 1st non-null of latest replaces the string from list A (outer List.Transform). We can do in this way


    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}?)



Related questions

Browse Categories