Checking records with list of similar words

[brandonchan]

Hi All,

I have below table and would like to check if the searched content exists in Table 1 db record. But I would like to check Table 2 Keywords first for some similar words, text contains seem not applicable coz the keywords may not be contained in full words. May I know how can I do that and join the search content and db? Great thanks.

Searched content:

"Great Sample Ltd."

Table 1 - db

#     Company Name

1     Testing Incorporation

2     Nice Association

3     Great Sample Limited

4     Transform Limited

5     Great Idea Limited

Table 2 - Keywords

Keyword 1        Keyword 2

Ltd.                   Limited

[v-yingjl]

Hi @brandonchan ,

You can create a parameter for your search content and try this query:

= Table.AddColumn(
#"Changed Type", "Search result", each 
let 
a = if Text.Contains(#"Searched content",#"Keywords"[Keyword 1]{0}) then Text.Replace(#"Searched content",#"Keywords"[Keyword 1]{0},#"Keywords"[Keyword 2]{0}) else null
in 
if Text.Contains([Company Name],a) then a else "Not match"
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brandonchan]

Hi @v-yingjl 

Thanks for your reply and it really comes close. But what if I have multiple rows of keywords? I found it will occur error if I have more than 1 row of keywords,

Thank you

[v-yingjl · Accepted solution]

Hi @brandonchan ,

You can create a custom column like this to replace multiple values of a list at the same time if there are multiple values in the keyword table:

= Table.AddColumn(#"Changed Type", "Search result", each 
    let a = if List.ContainsAll(Text.Split(#"Searched content"," "), Keywords[Keyword 1]) then  
         List.Accumulate(
             List.Numbers(0,Table.RowCount(Keywords)),
             #"Searched content",
             (state, current) => 
               Text.Replace(state,Keywords[Keyword 1]{current},
               Keywords[Keyword 2]{current})
         ) else null
    in 
    if Text.Contains([Company Name],a) then a else "Not match"
)

Please refer: Multiple Find / Replace with List.Accumulate() ~ Power Query 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brandonchan]

Hi @v-yingjl 

Just wanna know if the searching could be base on "Contain" condition but not exactly match the searched content.

For Example, if I put a search "Great Sample", or "Great Sample Limited", it will return null. May I have you assistance on that?

Thank you

[watkinnc]

Text.Contains actually does allow partial string search within the text.  Let's say you have the tables db, Keywords, and Search.  After the last step in your Search table, type this into the formula bar:

= Table.AddColumn(Source, "Keyword Result", each if Text.Contains([Searched Content], Keywords[Keyword 1]{0}) then Table.FindText(db, Keywords[Keyword 2]{0}) else "No Match")

Once you expand the resulting column, you'll have:

You will still need to filter to the actual result that you wish to keep.

---Nate

[brandonchan]

Hi Nate,

Thanks for your reply.

Is that possible to only return "Great Sample Limited" as result? Seems now your expression only considers the keywords table but not also look at "Great Sample". And 2nd question is, can we cater more than 1 search content in search table?

Thanks.