Power Query formula for partial matches

[brawnystaff]

Tried using the following column/formula to look for matches in multiple fields (columns) based on a list of keywords:

List.ContainsAny({[Column1],[Column2],[Column3]},Keywords)

It does work, but only in cases where the entire cell matches the list of keywords. Looking for an alternative to show partial matches as well (e.g. "the" would find there, their, them, etc.). Any ideas? I known I can create a conditional column with various Text.Contains and elseif statements, but given number of columns and keywords that is time consuming to type up.

Thanks.

 

[GraH]

What is inside Column1..Column3? Single words, phrases?
Does the partial match need to be case sensitive or not?

 

[brawnystaff]

Phrases/short sentences..

Looking for it to be not case sensitive.

Thanks.

 

[GraH]

Hi, I don't have time to create and test with some dummy data, but last year I was doing a similar text analysis for work. I found inspiration in these resources:

https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query

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

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 li...

stackoverflow.com

 

[brawnystaff]

Thanks. Had seen the MyOnlineTrainingHub link before, but each formula only does partial matches to one column. Regarding the other link, could not get that one to work either, but do believe perhaps there should be some type of Custom Function to make this work.

At this point, the work around I have used is to merge all columns into one column and use the following formula on it

List.Count(Splitter.SplitTextByAnyDelimiter(Keywords)([MergedColumns]))>1