Results 1 to 5 of 5

Thread: Look if Keywords in one Dataset contain in the Second Dataset

  1. #1

    Look if Keywords in one Dataset contain in the Second Dataset



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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"
    )


  2. #2

  3. #3
    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.
    Click image for larger version. 

Name:	2020-06-10_165744.jpg 
Views:	1 
Size:	22.5 KB 
ID:	9849
    Regards,
    Last edited by p45cal; 2020-06-10 at 05:59 PM.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,836
    Articles
    0
    Excel Version
    O365
    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.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,843
    Articles
    0
    Excel Version
    365
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •