Find String from One List within Another List and Return String Found

3
votes

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.

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 this:

    List
1   yes
2   yes
3   no
4   yes

I want the query results to be:

    List
1   roo
2   me
3   
4   only

Any idea how to make it so?

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

Thanks

2
powerbipowerquerym

2 Answers

4
votes

I would do this way:

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

[edited]

The idea is to use List.Transform twice: inner one changes list B to leave only matching values. Then 1st non-null of latest replaces string from list A (outer List.Tramsform).

2
votes

Edit: I think you switched the first 2 elements of the result?

You can use the following code:

let
    ListA = {"help me rhonda",  "in my room", "good vibrations", "god only knows"},
    ListB = {"roo", "help", "me", "only"},
    TableA = Table.FromList(ListA,null,{"ListA"}),
    AddedListBMatches = Table.AddColumn(TableA, "ListBMatches", (x) => List.Select(ListB, each Text.PositionOf(x[ListA], _) >= 0)),
    ExtractedValues = Table.TransformColumns(AddedListBMatches, {"ListBMatches", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Result = ExtractedValues[ListBMatches]
in
    Result

The "ExtractedValues" step is the result of pressing the expand button in the header of the "ListBMatches" column and choose Extract Values, comma separated. This option was added in the January 2017 update.

I added "help" to ListB so the first element of ListA has 2 matches that are both returned.