Look if Keywords in one Dataset contain in the Second Dataset

[absherzad]

Hi All,
How it is possible in Power Query to partially search if a value in one dataset exists in the second one. I want to replace the following below approach:

if  Text.Contains([Column in 2nd Dataset], "Keyword 1") or 
    Text.Contains([Column in 2nd Dataset], "Keyword 2") or 
    Text.Contains([Column in 2nd Dataset], "Keyword 3") or 
    Text.Contains([Column in 2nd Dataset], "Keyword 4") 
then 
    "Matched" 
else 
    "Not Matched")

In summary, I want the following DAX formula in Power Query:

=IF (
    SUMX ( MatchList, SEARCH( Dataset1[Keywords], Dataset2[Column in 2nd Dataset], 1, 0 ) ) > 0,
    "Matched",
    "Not Matched"
)

[Bob Phillips]

Two suggestions

https://blog.crossjoin.co.uk/2014/06...n-power-query/

or

http://www.thebiccountant.com/2016/0...d-power-query/

[absherzad]

Thanks for the reply,
I already read these examples but I could not figure out these methods due to the following reasons:

• It is not supposed to replace the original text,
• The Keywords are more than one word,
• Once there is a partial matches instead of simply returning "Matched" or "Not Matched", I may perform other text manipulation to extract substrings from the original text.

Please refer to the attached screenshot for more clarification.

Regards,

[Bob Phillips]

You could still use those methods. A simple way is to check if the new text is the same as the old text after replacing, if not it is Matched, else it is not matched. You can still do further manipulation, as much as you like.

[p45cal]

I realise this thread is about 3 years old; I'm just using these old threads as practice.
The attached contains a couple of (similar) solutions including:

let
    Dataset1 = Excel.CurrentWorkbook(){[Name="Dataset1"]}[Content],
    Dataset2 = Excel.CurrentWorkbook(){[Name="Dataset2"]}[Content],
    AddedCustom = Table.AddColumn(Dataset1, "Custom", each Dataset2),
    ExpandedCustom = Table.ExpandTableColumn(AddedCustom, "Custom", {"Keywords"}, {"Keywords"}),
    AddedCustom1 = Table.AddColumn(ExpandedCustom, "Custom", each Text.Contains([Master Data],[Keywords])),
    GroupedRows = Table.Group(AddedCustom1, {"Master Data"}, {{"Count", each (if List.AnyTrue([Custom]) then "" else "Not ") & "Matched"}})
in
    GroupedRows