Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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
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.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.