cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WebPortal
Post Prodigy
Post Prodigy

Check words in common row by row

Hello,

 

My collection looks like this:

 

RowID  | TextA           | TextB

1          |  ABC   DEF   | XXd ABC

2          | Xlso   LoL    | FFF ABC

...

 

I want to check Row by Row if TextA and TextB have any words in common (and which words).

 

Thus, I'm trying:

 

ForAll(MyCollection, If( IsMatch(TextA, TextB, Collect(_words, ....)))

 

But IsMatch expects a regex, and this isn't working.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

@WebPortal 

No semicolon trailing version:

ClearCollect(words,
    DropColumns(
        AddColumns(
            GroupBy(
                Ungroup(
                    Filter(
                        ForAll(MyCollection, 
                            With({word:Split(TextA, " ")},
                                Filter(
                                    ForAll(word, If(Result in TextB, {id: RowID, word:Result})
                                    ),
                                    !IsBlank(word)
                                )
                            )
                        ),
                        !IsEmpty(Value)
                    ),        
                "Value"
                ), 
                "id", 
                "recs"
            ),
            "words", With({_vals:Concat(recs, word & "; ")}, Left(_vals, Len(_vals)-2))
        ),
        "recs"
    )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

8 REPLIES 8
RandyHayes
Super User III
Super User III

@WebPortal 

IsMatch is not really going to work well like that.

You need to separate out all the words in TextA in order to compare them to the TextB.

 

This formula will give you a collection called words with a record that contains an id column with the row id that had a match, and a word column that will have a semicolon separated list of the words that matched from TextA to TextB:

ClearCollect(words,
    DropColumns(
        AddColumns(
            GroupBy(
                Ungroup(
                    Filter(
                        ForAll(MyCollection, 
                            With({word:Split(TextA, " ")},
                                Filter(
                                    ForAll(word, If(Result in TextB, {id: RowID, word:Result})
                                    ),
                                    !IsBlank(word)
                                )
                            )
                        ),
                        !IsEmpty(Value)
                    ),        
                "Value"
                ), 
                "id", 
                "recs"
            ),
            "words", Concat(recs, word & "; ")
        ),
        "recs"
    )
)

 

 I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
WebPortal
Post Prodigy
Post Prodigy

Whoa!

 

Almost there!

 

I just need to remove the last ;

RandyHayes
Super User III
Super User III

@WebPortal 

No semicolon trailing version:

ClearCollect(words,
    DropColumns(
        AddColumns(
            GroupBy(
                Ungroup(
                    Filter(
                        ForAll(MyCollection, 
                            With({word:Split(TextA, " ")},
                                Filter(
                                    ForAll(word, If(Result in TextB, {id: RowID, word:Result})
                                    ),
                                    !IsBlank(word)
                                )
                            )
                        ),
                        !IsEmpty(Value)
                    ),        
                "Value"
                ), 
                "id", 
                "recs"
            ),
            "words", With({_vals:Concat(recs, word & "; ")}, Left(_vals, Len(_vals)-2))
        ),
        "recs"
    )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

WebPortal
Post Prodigy
Post Prodigy

Brilliant!

WebPortal
Post Prodigy
Post Prodigy

@RandyHayes 

Just a small detail:

If TextA is: "THIS A TOMORROW"

And TextB is: "WHATEVER TOMORROW"

 

Then words is: "A; TOMORROW"

 

Because A is in TextB.

 

So this is matching from A is in B by characters and not entire words, I think.

RandyHayes
Super User III
Super User III

@WebPortal 

Picky picky!!! LOL.

 

Yes, that is a correct observation.

 

Change formula to the following:

ClearCollect(words, 
    DropColumns(
        AddColumns(
            GroupBy(
                Ungroup(
                    Filter(
                        ForAll(MyCollection, 
                            With({word:Split(TextA, " ")},
                                Filter(
                                    ForAll(word, If(Result in Split(TextB, " ").Result, {id: rowID, word:Result})
                                    ),
                                    !IsBlank(word)
                                )
                            )
                        ),
                        !IsEmpty(Value)
                    ),        
                "Value"
                ), 
                "id", 
                "recs"
            ),
            "words", With({_vals:Concat(recs, word & "; ")}, Left(_vals, Len(_vals)-2))
        ),
        "recs"
    )

    })

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!
WebPortal
Post Prodigy
Post Prodigy

@RandyHayesYOU ROCK!

 

(just remove that last curly parentheses at the end of the expression, for the sake of good order...)

 

🙂

RandyHayes
Super User III
Super User III

@WebPortal 

Oops...how did that get in there?  Yes, remove that!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (88,649)