Look if Keywords in one Dataset contain in the Second Dataset

absherzad

absherzad

New member
Joined
Jul 27, 2017
Messages
3
Reaction score
0
Points
0
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:
Code: 
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:
Code: 
=IF (
    SUMX ( MatchList, SEARCH( Dataset1[Keywords], Dataset2[Column in 2nd Dataset], 1, 0 ) ) > 0,
    "Matched",
    "Not Matched"
)

 
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.
2020-06-10_165744.jpg
Regards,
 
Last edited by a moderator:
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.
 
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:
Code: 
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
 

Attachments

  • ExcelGuru8118.xlsx
    20.6 KB · Views: 7
You must log in or register to reply here.
Back
Top